Expand my Community achievements bar.

SOLVED

How to targetData from QueryDef alias?

Avatar

Level 2

Hi,

I'm attempting to display the result of the 'seasons' alias in a delivery using targetData. However the delivery fails due to 'targetData not defined'.

Please can you tell me how it's possible to do this?

The workflow appears as below:

1535336_pastedImage_8.png

The JavaScript logs the count correctly:

1535337_pastedImage_14.png

I there a specific way to target an alias from a queryDef in the delivery?

Many thanks,

Rob

JAVASCRIPT

var query = xtk.queryDef.create(

  <queryDef schema="temp:query2" operation="select">

   <select>

     <node expr="Countdistinct(@email)" alias="seasons"/>

   </select>

</queryDef>).ExecuteQuery();

var results = query.query2;

logInfo("Count: " +results.seasons);

DELIVERY

Seasons: <%= targetData.seasons %>

1 Accepted Solution

Avatar

Correct answer by
Employee

HI robertw937098,

Inside your JS code at the end, add the following line

instance.vars.seasons = results.seasons;

Then add an Enrichment activity between JS code and Delivery.

Inside the Enrichment, click on Add data and select "Data linked to filtering dimension". Click Next

1535688_pastedImage_4.png

Again select "Data of the filtering dimension" and click Next

1535689_pastedImage_5.png

On the right hand side Click on Add to add a new expression and set the expression to $(instance/vars/@seasons)

1535696_pastedImage_6.png

Also mention the alias to be @seasons.

Now you can use targetData.seasons inside your delivery.

Regards,
Vipul

View solution in original post

7 Replies

Avatar

Employee

Hi Robert,

Can you please right click the transition after the delivery and see the schema tab to see which columns are present.

Also looking at the workflow , I guess if you add an enrichment with the same expression and an alias for the enrichment it should pick up the alias name. Make sure to define the alias with the @ symbol.

Regards,

Adhiyan

Avatar

Correct answer by
Employee

HI robertw937098,

Inside your JS code at the end, add the following line

instance.vars.seasons = results.seasons;

Then add an Enrichment activity between JS code and Delivery.

Inside the Enrichment, click on Add data and select "Data linked to filtering dimension". Click Next

1535688_pastedImage_4.png

Again select "Data of the filtering dimension" and click Next

1535689_pastedImage_5.png

On the right hand side Click on Add to add a new expression and set the expression to $(instance/vars/@seasons)

1535696_pastedImage_6.png

Also mention the alias to be @seasons.

Now you can use targetData.seasons inside your delivery.

Regards,
Vipul

Avatar

Level 2

This worked. Thanks a lot!

I was hoping the JavaScript would create the targetData field without needing an enrichment.

Avatar

Level 2

Hi Vipul,

How can I make an instance variable a number data type?

It seems to default to a string.

Thanks,

Rob

Vipul Raghav

Avatar

Employee

Hi Rob,

You can open the expression editor and there you will find all the existing in house formulaes.

Once such formula is ToInteger. Please make use of it to convert the instance variable to integer

1581247_pastedImage_0.png

Regards,
Vipul

Avatar

Level 2

Thanks Vipul, that's very helpful.

Are you able to help with the below script please? My issue is the 'Average' (integer) instance variable is giving the same value for all rows. Rather than working out the average and giving that value to each recipient.

The calculations are correct, but it only outputs the value for one row then gives rows that value. E.g. In the below screenshot all rows are given 110 for Average. This is correct for the bottom customer, but not the top. Hope that makes sense.

1581540_pastedImage_0.png

var query = xtk.queryDef.create(

<queryDef schema="temp:enrich112" operation="select">

   <select>

     <node expr="@difference1"/>

     <node expr="@difference2"/>

     <node expr="@difference3"/>

   </select>

  </queryDef>);

var res = query.ExecuteQuery();

for each (var row in res)

{

var difference1 = parseInt(row.@difference1);

logInfo("Difference 1: " + difference1);

var difference2 = parseInt(row.@difference2);

logInfo("Difference 2: " + difference2);

var difference3 = parseInt(row.@difference3);

logInfo("Difference 3: " + difference3);

var total = difference1 + difference2 + difference3;

logInfo("Total of days between: " + total);

var average = total / 3;

logInfo("Average: " + average);

logInfo(typeof average);

var rounded_average = Math.round(average);

logInfo(typeof rounded_average);

logInfo(rounded_average);

instance.vars.average = rounded_average;

}

Avatar

Level 5

Hi Vipul,

Can I ask, is there anyway to make an instance variable a memo/long text data type?

I have a situation where I have some SMS content (using an adhoc solution, not the mobile channel) which I'm going to use a JS activity to shorten some URLs via API - and store the result for each record - which I'll then output to the SMS provider as a data file extract... but obviously the SMS content could well exceed 255 characters.

Please let me know if this is possible.

Thanks

David