Not contained in either aggregate function or GROUP BY clause | Community
Skip to main content
R
rvnth
Level 5
June 12, 2024
Question

Not contained in either aggregate function or GROUP BY clause

  • June 12, 2024
  • 1 reply
  • 602 views

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()
This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

1 reply

vraghav
Adobe Employee
vraghavAdobe Employee
Adobe Employee
June 19, 2024

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()
    R
    rvnthAuthor
    Level 5
    June 20, 2024

    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

     

     

    Terms & ConditionsAccessibility statement

    Loading footer...