Hi there,
I tried using the script below in the JS activity, but I encountered the error: 'it is not contained in either aggregate function or GROUP BY clause.' I am now attempting to use a subquery and will update you on the progress. Please can you let me know if it can be achievable without using subquery.
var fquery = xtk.queryDef.create ( <queryDef schema = {vars.targetSchema} operation = "select" > <select> <node expr="Substring(@segmentCode,'_',1)" alias="SegmentCode"/> <node expr="Sum(Case(When(@segmentCode LIKE '%_IS_DELETED', 1), Else(0)))" alias="@IS_DELETED"/> </select> <groupBy> <node expr="Substring(@segmentCode,'_',1)"/> </groupBy> </queryDef> ); var itemsXMLF = fquery.ExecuteQuery(); // DOMElement var itemsXMLFStr2 = itemsXMLF.toString(); vars.itemsXMLFString2 = itemsXMLF.toXMLString(); //logInfo("itemsXMLF = " + itemsXMLF.toString()
Views
Replies
Total Likes
I think the definition of Substring function is incorrect. Second argument has to be offset and you have passed a character.
I couldn't understand the full use case you are trying to achieve but please try this code
var fquery = xtk.queryDef.create
(
<queryDef schema = {vars.targetSchema} operation = "select" >
<select>
<node expr="Substring(@segmentCode,Charindex(@segmentCode,'_' )+1,1)" alias="SegmentCode"/>
<node expr="Sum(Case(When(@segmentCode LIKE '%_IS_DELETED', 1), Else(0)))" alias="@IS_DELETED"/>
</select>
<groupBy>
<node expr="Substring(@segmentCode,Charindex(@segmentCode,'_' )+1,1)"/>
</groupBy>
</queryDef>
);
var itemsXMLF = fquery.ExecuteQuery(); // DOMElement
var itemsXMLFStr2 = itemsXMLF.toString();
vars.itemsXMLFString2 = itemsXMLF.toXMLString();
//logInfo("itemsXMLF = " + itemsXMLF.toString()
Hi @vraghav ,
Thank you for looking in to this. I have a column named segment_code containing values like scen3_IS_DELETED, scen3_IS_EMAIL_OPT_IN, and scen4_IS_DELETED.
My goal is to extract the substring before the first underscore and display it in one column.
Additionally, I want to count the occurrences of IS_DELETED or IS_EMAIL_OPT_IN in separate columns for each segment.
var fquery = xtk.queryDef.create
(
<queryDef schema = {vars.targetSchema} operation = "select" >
<select>
<node expr="Right(@segmentCode, Length(@segmentCode) - Charindex(@segmentCode,'_'))" alias="@segmentCode"/>
<node expr="Sum(Iif(Right(@segmentCode, Length(@segmentCode) - Charindex(@segmentCode,'_')) = 'IS_DELETED', 1,0))" label="IS_DELETED_RECIPIENT" alias="@IS_DELETED_RECIPIENT"/>
<node expr="Sum(Iif(Right(@segmentCode, Length(@segmentCode) - Charindex(@segmentCode,'_')) = 'IS_EMAIL_OPT_IN', 1,0))" label="IS_EMAIL_OPT_IN" alias="@IS_EMAIL_OPT_IN"/>
<node expr="Sum(Iif(Right(@segmentCode, Length(@segmentCode) - Charindex(@segmentCode,'_')) = 'IS_PRIMARY_EMAIL_INVALID', 1,0))" label="IS_PRIMARY_EMAIL_INVALID" alias="@IS_PRIMARY_EMAIL_INVALID"/>
</select>
<groupBy>
<node expr="@segmentCode"/>
</groupBy>
</queryDef>
I have implemented the above logic, which appears to work, but the generated output includes multiple occurrences of scen3d or scen3f.
itemsXMLF = <union422-collection> <union422 CUSTOMER_TYPE_DESC="0" IS_CURRENT_OWNER="734" IS_DELETED_RECIPIENT="0" IS_DELETE_VEHICLE="0" IS_EMAIL_OPT_IN="0" IS_PRIMARY_EMAIL_INVALID="0" IS_RDR_CANCEL="0" RECIPIENT_EMAIL="0" SALES_ANALYSIS_DATE="0" Segment="Scen3d" UVO_REGISTRATION_LOGIN_NAME="0" UVO_SUBSCRIPTION_DATE="0"/> <union422 CUSTOMER_TYPE_DESC="0" IS_CURRENT_OWNER="0" IS_DELETED_RECIPIENT="0" IS_DELETE_VEHICLE="0" IS_EMAIL_OPT_IN="0" IS_PRIMARY_EMAIL_INVALID="0" IS_RDR_CANCEL="20" RECIPIENT_EMAIL="0" SALES_ANALYSIS_DATE="0" Segment="Scen3d" UVO_REGISTRATION_LOGIN_NAME="0" UVO_SUBSCRIPTION_DATE="0"/> <union422 CUSTOMER_TYPE_DESC="28" IS_CURRENT_OWNER="0" IS_DELETED_RECIPIENT="0" IS_DELETE_VEHICLE="0" IS_EMAIL_OPT_IN="0" IS_PRIMARY_EMAIL_INVALID="0" IS_RDR_CANCEL="0" RECIPIENT_EMAIL="0" SALES_ANALYSIS_DATE="0" Segment="Scen3d" UVO_REGISTRATION_LOGIN_NAME="0" UVO_SUBSCRIPTION_DATE="0"/> <union422 CUSTOMER_TYPE_DESC="1" IS_CURRENT_OWNER="0" IS_DELETED_RECIPIENT="0" IS_DELETE_VEHICLE="0" IS_EMAIL_OPT_IN="0" IS_PRIMARY_EMAIL_INVALID="0" IS_RDR_CANCEL="0" RECIPIENT_EMAIL="0" SALES_ANALYSIS_DATE="0" Segment="Scen3f" UVO_REGISTRATION_LOGIN_NAME="0" UVO_SUBSCRIPTION_DATE="0"/> <union422 CUSTOMER_TYPE_DESC="0" IS_CURRENT_OWNER="0" IS_DELETED_RECIPIENT="0" IS_DELETE_VEHICLE="0" IS_EMAIL_OPT_IN="0" IS_PRIMARY_EMAIL_INVALID="0" IS_RDR_CANCEL="0" RECIPIENT_EMAIL="0" SALES_ANALYSIS_DATE="0" Segment="Scen3d" UVO_REGISTRATION_LOGIN_NAME="154" UVO_SUBSCRIPTION_DATE="0"/> <union422 CUSTOMER_TYPE_DESC="0" IS_CURRENT_OWNER="3" IS_DELETED_RECIPIENT="0" IS_DELETE_VEHICLE="0" IS_EMAIL_OPT_IN="0" IS_PRIMARY_EMAIL_INVALID="0" IS_RDR_CANCEL="0" RECIPIENT_EMAIL="0" SALES_ANALYSIS_DATE="0" Segment="Scen3f" UVO_REGISTRATION_LOGIN_NAME="0" UVO_SUBSCRIPTION_DATE="0"/> <union422 CUSTOMER_TYPE_DESC="0" IS_CURRENT_OWNER="0" IS_DELETED_RECIPIENT="0" IS_DELETE_VEHICLE="0" IS_EMAIL_OPT_IN="0" IS_PRIMARY_EMAIL_INVALID="0" IS_RDR_CANCEL="0" RECIPIENT_EMAIL="0" SALES_ANALYSIS_DATE="0" Segment="Scen3f" UVO_REGISTRATION_LOGIN_NAME="8" UVO_SUBSCRIPTION_DATE="0"/>
However, the expected output should be structured as shown in the screen shot,
but I am seeing the output in my email as below
Views
Replies
Total Likes
Views
Likes
Replies