Combination of Set analysis and IF statement in Script

satish25 — Sat, 16 Nov 2024 08:26:31 GMT

Hello All,

I wanted to write the below expression in the Load Editor.

if(Technology='Mobile',Sum({<Device={'ABC'}>}Sales),Sum({<Device={'XYZ'}>}Sales))

Thanks in advance.

Re: Combination of Set analysis and IF statement in Script

petter — Mon, 09 Apr 2018 05:35:21 GMT

Set expressions are not supported in the load script. You can only use it in the applications "User Interface" expressions.

You can achieve what you want by using aggregations anyway without the set expressions or by splitting the values into two different fields. The choice of approach is dependent on what your data model looks like and what you have done already in your load script:

1) With aggregation Sum and a mandatory GROUP BY:

LOAD

Field1,

....

FieldN,

If( Technology ='Mobile' AND Device='ABC', Sum(Sales)) AS ABC_Sales,

If( Technology <>'Mobile' AND Device='XYZ', Sum(Sales)) AS XYZ_Sales

FROM .......

GROUP BY

Field1, ....., FieldN; // All the non-aggregation fields that is also present above in the beginning of the LOAD

Probably you dont even need to do aggregation in the load script. The aggregation (in this case Sum) is appropriate and necessary in expressions in the UI since set expressions cant be used except within an aggregation function.

2) Simple approach when summing is not necessary:

LOAD

.....

If( Technology ='Mobile' AND Device='ABC', Sales) AS ABC_Sales,

If( Technology <>'Mobile' AND Device='XYZ', Sales) AS XYZ_Sales

FROM

....

;

topic Combination of Set analysis and IF statement in Script in App Development

Combination of Set analysis and IF statement in Script

Re: Combination of Set analysis and IF statement in Script