Combination of Set analysis and IF statement in Script

[satish25]

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.

[petter]

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

  ....

;