4 Replies Latest reply: Aug 16, 2017 7:38 AM by Liv ma RSS

    Equivalent for AGGR in script

    Liv ma

      Hello

       

      I want to calculate in the script a dimension that I would do in a chart using the AGGR function and I cannot due that because AGGR does not work in the script

       

      so here is what I want to do:

       

      suppose I have the following data structure: Client, Region, Sales

       

      I want to be able to identify all clients with sales above a certain value and I want to mark them directly in the table through a calculated dimension: if the sum of all sales per customer is larger than X, then flag the client

       

      in the chart I would use if(aggr(sum(sales),client)>=1000,'Flag','Non-flag') but this does not work in the script

       

      any help is kindly appreciated

       

      I assume it can be done with resident tables but I do not know how to do it

      LM

        • Re: Equivalent for AGGR in script
          Ruben Marin

          Hi Liv, the Aggr creates a temporal table with the dimensions used as parameters, you can replicate this in script using group By:

           

          LOAD client,

               If(Sum(sales)>=1000,'Flag','Non-Flag') as FlagField

          Resident TableName

          Group by client;

            • Re: Equivalent for AGGR in script
              Liv ma

              hi, thank you for the reply

              does this table need to be a separate table linked with the original one via Client ID or is there a way to bring this flag into the original table and drop this table?

                • Re: Equivalent for AGGR in script
                  Ruben Marin

                  Hi, you can left as a separate table but usually is better to set flags in the same table.

                   

                  Also it's recomended to use '0' and '1' to flag, it's smaller.

                   

                  You can use Join or Map:

                   

                  Join version (links by client field)

                  Left Join (MainTableName)

                  LOAD client,

                      If(Sum(sales)>=1000,1,0) as FlagField

                  Resident MainTableName

                  Group by client;

                   

                  Map version (use mapping table with the client values and applymap to look for the value)

                  mapFlag:

                  Mapping LOAD client as ValueToSearch,

                      If(Sum(sales)>=1000,1,0) as ValueToReturn

                  Resident MainTableName

                  Group by client;

                   

                  RENAME MainTableName to MainTableNameAux:


                  MainTableName:

                  LOAD *,

                      ApplyMap('mapFlag', client, Null()) as FlagField

                  resident MainTableNameAux;


                  DROP Table MainTableNameAux;