Expand my Community achievements bar.

Announcing the launch of new sub-community for Campaign Web UI to cater specifically to the needs of Campaign Web UI users!

queryDef Script Instance Variable Problem!

Avatar

Level 2

Hi,

Are you able to help with the below script please? I have created an integer instance variable called 'Average'. The problem is that variable is outputting 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.

1581541_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;

}

11 Replies

Avatar

Employee

Hi robertw937098 ,

The instance variable value is getting overridden in your script. It will always contain the average difference of the last customer.

To calculate the average for each row, you can use the Enrichement activity using below steps.

1. In "Add additional data", add a new output column for average.

2. Go to "Advanced Selections", then "Expression"

3. You will see the list of all the available functions.

4. You can use sum and calculate the average.

Thanks,

Nainy Jain

Avatar

Level 2

Thanks Nainy Jain for responding.

I need to calculate the average for three different fields. As far as I'm aware, the aggregate functions only allow one field. This is why I attempted it via JavaScript.

Is it possible to work out the average with 3 different fields? What's the syntax?

If not, how can I stop the code from overwriting?

Many thanks,

Rob

Avatar

Employee

Hi robertw937098 ,

Please follow the syntax as below:

1583211_pastedImage_0.png

I have calculated the average of 2 values, you can use below formula for 3 values:

([target/@diff1]+[target/@diff2]+[target/@diff3])/3

Also, you can see the average field(converted to Integer) in display target:

1583212_pastedImage_2.png

Thanks,

Nainy Jain

Avatar

Level 2

Thanks a lot, this is really helpful. One issue left...

To troubleshoot, I'm doing the total then average in separate enrichment actives. When I do this total expression (@difference1+@difference2+@difference3+@difference4+@difference5) the total column returns 0 when some of the columns are 0, as you can see in the below data.

E.g. 10 + 10 + 0 = 0. Whereas, it should return 20.

Do you know how to resolve this?

1583197_pastedImage_0.png

Thanks,

Rob

Avatar

Employee

Hi Rob,

I am not able to reproduce the issue in my system.

1583273_pastedImage_0.png

Please check if all the difference attributes are of type "long" or "double" in your schema.

Thanks,

Nainy Jain

Avatar

Level 2

Thanks for checking. The difference fields are all Integer (32 bit) format.

1583265_pastedImage_0.png

Avatar

Level 2

I see the cause. It's due to blanks in the journey fields.

1583266_pastedImage_0.png

I am populating the differences fields with the days between the dates:

1583288_pastedImage_1.png

Avatar

Employee

Hi Rob,

In this case, the difference field should be populated as integer default value 0 and should work fine.

Thanks,

Nainy Jain

Avatar

Level 2

I have fixed this, an integer defaults as 0 if there's no data. With the default 0 sums cannot be made. Therefore I used the below condition to insert a numerical 0 for sums.

Iif(@difference1='',0,@difference1)

Avatar

Level 2

Hi Nainy,

I realised I have implemented this incorrectly as I targeting the date data from recipient level. Whereas, for accurate data I needed to change dimension to Journey level.

My question is, how is it possible to do a DaysDiff(@OUT_DATE_DEP) function on all the data in one date field per recipient?

E.g. The below customer has many out date dep dates.

1583580_pastedImage_1.png

Many Thanks,

Rob