1 Reply Latest reply: Apr 9, 2018 1:35 AM by Petter Skjolden RSS
      • Re: Combination of Set analysis and IF statement in Script
        Petter Skjolden

        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

          ....

        ;