Solved

Invert Data

Forum|Forum|8 years ago
March 21, 2017
13 replies
6109 views

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.

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

Best answer by Amit_Kumar

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

alistairk396804Author

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

vraghav

Adobe Employee

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.

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

Hi Alistair,

did you tried following:

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

Regards,

Amit

alistairk396804Author

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>

alistairk396804Author

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

vraghav

Adobe Employee

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

alistairk396804Author

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_KumarAccepted 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

Show more replies

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded