queryDef Script Instance Variable Problem!

[robertw937098]

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.

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;

}

[nainyjain]

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

[robertw937098]

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

[nainyjain]

Hi robertw937098 ,

Please follow the syntax as below:

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:

Thanks,

Nainy Jain

[robertw937098]

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?

Thanks,

Rob

[nainyjain]

Hi Rob,

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

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

Thanks,

Nainy Jain

[robertw937098]

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

[robertw937098]

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

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

[nainyjain]

Hi Rob,

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

Thanks,

Nainy Jain

[robertw937098]

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)

[robertw937098]

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.

Many Thanks,

Rob

[pablo_rosero1]

Hi nainyjain​! Could you check on this?

Thank you