Not contained in either aggregate function or GROUP BY clause

[rvnth]

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()

[vraghav]

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()

[rvnth]

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