Highlighted

queryDef Script Instance Variable Problem!

robertw937098

28-09-2018

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;

}

Replies

Highlighted

nainyjain

Employee

01-10-2018

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

Highlighted

robertw937098

01-10-2018

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

Highlighted

nainyjain

Employee

01-10-2018

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

Highlighted

robertw937098

01-10-2018

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

Highlighted

nainyjain

Employee

01-10-2018

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

Highlighted

robertw937098

01-10-2018

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

Highlighted

nainyjain

Employee

01-10-2018

Hi Rob,

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

Thanks,

Nainy Jain

Highlighted

robertw937098

01-10-2018

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)