Question
Customized report of incomplete data
Hi there,
I'm currently working on creating a report that includes counts for various scenarios where data is incomplete or conditions are not met. I've managed to generate a report, but I'm aiming for a format similar to the screenshot below for the final version.
var fquery = xtk.queryDef.create
(
<queryDef schema = {vars.targetSchema} operation = "select" >
<select>
<node expr="Substring(@segmentCode,0,Charindex(@segmentCode,'_'))" label="Segment" alias="@Segment"/>
<node expr="Sum(Iif(Right(@segmentCode, Length(@segmentCode) - Charindex(@segmentCode,'_')) = 'RECIPIENT_EMAIL', 1, 0))" label="RECIPIENT_EMAIL" alias="@RECIPIENT_EMAIL"/>
<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>
);
var itemsXMLF = fquery.ExecuteQuery(); // DOMElement
var itemsXMLFStr2 = itemsXMLF.toString();
vars.itemsXMLFString2 = itemsXMLF.toXMLString();
logInfo("itemsXMLF = " + itemsXMLF.toString());
var itemsXMLF2 = DOMDocument.fromXMLString(vars.itemsXMLFString2);
logInfo(itemsXMLF2);
var seg = itemsXMLF2.root.getElements();
logInfo(seg);
var uniqueSegments = seg.filter(function(value, index, self) {
return self.indexOf(value) === index;
});
logInfo(uniqueSegments);
//logInfo("itemsXMLF = " + itemsXMLF.toString());
For each record the column segment code will have value scen3_IS_EMAIL_OPTIN or Scen4_IS_DELETE or Scen3_IS_EMAIL_OPTIN
Thank you in advance