Invert Data

[alistairk396804]

Hi,

I have some data in this format;

Record  Var1    Var2 1       00001   00002

but only want to query a single variable in my query so;

Record  Var 1       00001 2       00002

Is there an easy way to do this? I'm looking online but all of the solutions I've found are far more complex and often incompatible with Campaign.

Better yet - if there was a way to take the array value like this;

(00001,00002,etc)

And do the same that would be a better solution. Ideally this needs to by dynamic and create as many new rows as there are possible variables.

[alistairk396804]

A bit more detail if it's helpful;

I am passing through a string of deliveryids in a soap call.

xtk.workflow.PostEvent('WKF129','signal','', <variables varName= {vars.myVar} />,false)

in this instance varName = 4242971,4241831

In my called workflow I want to take varName and transpose each result into a new row so I end up with the following;

Row varDevId 1   4242971 2   4241831

This would also need to be dynamic so that if I pass through multiple results in varName that it will create as many rows as needed.

Once I have this I intend to use this single variable to query against nms.delivery in order to pull the delivery details for these delivery IDs as below;

All online solutions use unsupported functions such as myVar.map (function())

[Vipul_Raghav]

Hi Alistair,

You can use the variable containing comma separated values directly along with "is included in" operator.

It will help you to query for all deliveries in one go.

Regards,

Vipul

[Amit_Kumar]

alistairk39680404 wrote...

A bit more detail if it's helpful;

I am passing through a string of deliveryids in a soap call.

1. xtk.workflow.PostEvent('WKF129','signal','', <variables varName= {vars.myVar} />,false)

in this instance varName = 4242971,4241831

In my called workflow I want to take varName and transpose each result into a new row so I end up with the following;

1. Row varDevId
2. 1 4242971
3. 2 4241831

This would also need to be dynamic so that if I pass through multiple results in varName that it will create as many rows as needed.

Once I have this I intend to use this single variable to query against nms.delivery in order to pull the delivery details for these delivery IDs as below;

All online solutions use unsupported functions such as myVar.map (function())

Hi Alistair,

did you tried following:

var listOfIds = NL.XTK.toString(varName).split(',');

Regards,

Amit

[alistairk396804]

Hi Vipul,

I did try the 'is included in' option straightaway, however I had no success in getting it to work. The screenshots below show the query and error, I've tried similar attempts using the split variable/casting as an integer and other various options. 

[Amit_Kumar]

Hi Alistair,

Please use the following and this will work in campaign workflow. and refer to the following package code for reference.

var listOfIds = String(vars.varName).split(','); logInfo("first value " + listOfIds[0] ); logInfo("Second value " + listOfIds[1] ); logInfo("third value " + listOfIds[2] );

<?xml version='1.0'?> <package author="Amit (amit)" buildDate="2017-03-27 09:14:42.117Z" buildNumber="8724" buildVersion="6.1"> <entities schema="xtk:workflow"> <workflow internalName="WKFTEST1" label="New workflow" modelName="newOpEmpty" order="1136014743" scenario-cs="Notification of the workflow supervisor (notifySupervisor)" schema="nms:recipient" simulation="false"> <activities> <end label="End" name="end" x="482" y="150"/> <signal label="External signal" name="signal" x="179" y="146"> <transitions> <done name="done" target="js"/> </transitions> </signal> <js label="JavaScript code" name="js" x="333" y="151"> <transitions> <done name="done" target="end"/> <error name="error"/> </transitions> <script>var listOfIds = String(vars.varName).split(','); logInfo("first value " + listOfIds[0] ); logInfo("Second value " + listOfIds[1] ); logInfo("third value " + listOfIds[2] );</script> </js> </activities> <variables/> <scenario _operation="none" internalName="notifySupervisor"/> <desc>Empty starting template to create a workflow for a marketing campaign</desc> <folder _operation="none" name="nmsWkfOperation"/> <operation _operation="none" internalName="OP328"/> </workflow> </entities> <entities schema="xtk:workflow"> <workflow internalName="WKF762" label="Default workflow" modelName="opEmpty" scenario-cs="Notification of the workflow supervisor (notifySupervisor)" schema="nms:recipient" simulation="false"> <activities> <start label="Start" name="start" x="189" y="107"> <transitions> <initial name="initial" target="js"/> </transitions> </start> <end label="End" name="end" x="491" y="110"> <initScript>xtk.workflow.PostEvent('WKFTEST1','signal','', &lt;variables varName= {vars.myVar} /&gt;,false)</initScript> </end> <js label="JavaScript code" name="js" x="338" y="109"> <transitions> <done name="done" target="end"/> <error name="error"/> </transitions> <script>vars.myVar = "1,2,3";</script> </js> </activities> <variables/> <scenario _operation="none" internalName="notifySupervisor"/> <folder _operation="none" name="nmsWkfOperation"/> <operation _operation="none" internalName="OP328"/> </workflow> </entities> </package>

one more thing is this will not work with standard query because this variable will be treated as string  you have to get the delivery ids using querydef script or if you know max number of variables then you can use following way:

<package author="Amit (amit)" buildDate="2017-03-27 09:53:13.990Z" buildNumber="8724" buildVersion="6.1"> <entities schema="xtk:workflow"> <workflow internalName="WKF762" label="Default workflow" modelName="opEmpty" scenario-cs="Notification of the workflow supervisor (notifySupervisor)" schema="nms:recipient" simulation="false"> <activities> <start label="Start" name="start" x="189" y="107"> <transitions> <initial name="initial" target="js"/> </transitions> </start> <end label="End" name="end" x="491" y="110"> <initScript>xtk.workflow.PostEvent('WKFTEST1','signal','', &lt;variables varName= {vars.myVar} /&gt;,false)</initScript> </end> <js label="JavaScript code" name="js" x="338" y="109"> <transitions> <done name="done" target="end"/> <error name="error"/> </transitions> <script>vars.myVar = "95563,95564,95565";</script> </js> </activities> <variables/> <scenario _operation="none" internalName="notifySupervisor"/> <folder _operation="none" name="nmsWkfOperation"/> <operation _operation="none" internalName="OP328"/> </workflow> </entities> <entities schema="xtk:workflow"> <workflow internalName="WKFTEST1" label="New workflow" modelName="newOpEmpty" order="1136014743" scenario-cs="Notification of the workflow supervisor (notifySupervisor)" schema="nms:recipient" simulation="false"> <activities> <end label="End" name="end" x="720" y="136"/> <signal label="External signal" name="signal" x="176" y="136"> <transitions> <done name="done" target="js"/> </transitions> </signal> <js label="JavaScript code" name="js" x="328" y="136"> <transitions> <done name="done" target="query"/> <error name="error"/> </transitions> <script>var listOfIds  = new Array(); for each (var sub in String(vars.varName).split(',')) listOfIds.push(parseInt(sub)); vars.listOfId1 = listOfIds[0]; vars.listOfId2 = listOfIds[1]; vars.listOfId3 = listOfIds[2];</script> </js> <query label="primary key is included in ToInteger($(vars/@listOfId1)), ToInteger($(vars/@listOfId2)), ToInteger($(vars/@listOfId3)), ToInteger($(vars/@listOfId4))" name="query" schema="nms:recipient" x="552" y="136"> <transitions> <result name="result" target="end"/> </transitions> <where filterName="backGroundFilterFrm" filteringSchema="nms:recipient"> <condition expr="@id IN (ToInteger($(vars/@listOfId1)), ToInteger($(vars/@listOfId2)), ToInteger($(vars/@listOfId3)), ToInteger($(vars/@listOfId4)))" internalId="3649241246"/> </where> <humanCond>Query: primary key is included in ToInteger($(vars/@listOfId1)), ToInteger($(vars/@listOfId2)), ToInteger($(vars/@listOfId3)), ToInteger($(vars/@listOfId4))</humanCond> </query> </activities> <variables/> <scenario _operation="none" internalName="notifySupervisor"/> <desc>Empty starting template to create a workflow for a marketing campaign</desc> <folder _operation="none" name="nmsWkfOperation"/> <operation _operation="none" internalName="OP328"/> </workflow> </entities> </package>

[alistairk396804]

Hi Amit,

Thanks for the response!

I have no issues with the split or the csv variable, My current process is exactly the same system you have mentioned above.

The downside to that method is that in the following query you need to define and interrogate each [] part of the array on it's own merit. You then also need to decide how many array variables you are willing to extend your query to look at (I went up to [49]). this lead to a large following query where I was searching for the pk= myArray[0] OR pk= myArray[1] OR pk = myArray[2] etc all the way up to [49]...  It works but there has to be a better way to do it.

If I can get Vipul's statement to work that would be a more elegant solution as it is a single line of query and would extend up to N possible deliveries.

[Amit_Kumar]

Thanks for the clarification! What i believe now you have to rely of query def to get the results.

In query activity I am not sure if we can convert this to a csv integer values for INCLUDED IN results. 

Let's wait for Him.

Regards,

Amit

[Vipul_Raghav]

Hi Alistair,

What Amit is saying is right. I tried the same on my local instance and Campaign is considering the comma separated string as a single entity,

Is included in needs the strings to be comma separated. 

A good idea will be to extract the deliveryIds something like this and then get delivery details in queryDef itself

vars.deliveryIds = "8368222,8339632,8339182,8299073,8299072"; var whereCondition = "<condition expr="@id IN ("+vars.deliveryIds+")/>";

Regards,

Vipul

[alistairk396804]

Thanks for the ideas.

If possible I'd like to keep the query element.

I have configured the passed through variable in the soap call to now be "," separated - example of logged variable being passed through (trailing spaces are also trimmed off);

"4384943","4383191","4383250","4383190","4380314","4380313","4383280"

However it still seems to not like this string when using the 'included in' operator within the query. As I understand it should be working now - or at least returning no matches.

I've tried casting the below as an int but it makes no difference. I assume it is failing as the PK is an integer but the variables in the csv string are probably classed as text therefore the join fails due to the mismatch in types.

Casting the PK as a string allows the query to run but returns zero matches.

[Amit_Kumar · Accepted solution]

Hi Alistair,

Here is your working solution.

var listOfIds  = new Array(); for each (var sub in String(vars.varName).split(',')) listOfIds.push(parseInt(sub)); vars.listOfIds = listOfIds;      

Regards,

Amit

[alistairk396804]

Hi Amit,

We reached a working conclusion at about the same time :) I simply reversed by query criteria and used a 'contains' operator. No need to actually split the variable.

I'll need to test this further but I suspect this is will work as required. Appreciate all the help received!

[Amit_Kumar]

alistairk39680404 wrote...

Hi Amit,

We reached a working conclusion at about the same time :) I simply reversed by query criteria and used a 'contains' operator. No need to actually split the variable.

I'll need to test this further but I suspect this is will work as required. Appreciate all the help received!

I know it will work but it will break if you pass null or empty values so it's better to split and cast to prevent the errors.

[alistairk396804]

Hi Amit,

Due to the way the Delivery IDs are matched and generated for the soap call there will never be any instances where a null or empty value is presented.

However building in redundancy is always a good idea so I will look to implement your solution. Thanks for the insight on the push command!