6 Replies Latest reply: May 4, 2012 8:38 AM by Miroslav Žebrák RSS

    Moving calculated dimension to script

    Miroslav Žebrák

      Hi, please, how can I move this calculated dimension to script part?

       

       

      =aggr (if(sum (Amount) <=200,'1. до 200 грн.',

       

      if(sum (Amount) >200 and sum (Amount)<=1000,'2. 200-1000 грн.',

       

      if(sum (Amount)>1000 and sum (Amount)<=5000,'3. 1000-5000 грн.',

       

      if(sum (Amount)>5000,'4. более 5000 грн.')))), EntityId)

       

       

      "Amount" is from table "AmountRank" and "EntityId" is in other 3 tables.

       

      I put to script only this for now. I did column "Interval" with theese intervals.

       

      if (Amount <=200,'1. до 200 грн.',

           if(Amount >200 and Amount <=1000,'2. 200-1000 грн.',

            if(Amount >1000 and Amount <=5000,'3. 1000-5000 грн.',

           if (Amount >5000,'4. более 5000 грн.')))) as Interval,

       

       

      Thank you!

        • Re: Moving calculated dimension to script
          Matthew Fryer

          Hi

           

          You'd first need to get both Amount and EntityId in the same table by joining the tables together. Once you have this you can use group by and your nested if functions like this:

           

           

          LOAD

              *,

              if(sum (Amount_sum) <=200,

                  '1. до 200 грн.'

              ,

                  if(sum (Amount) >200 and sum (Amount_sum)<=1000,

                      '2. 200-1000 грн.'

                  ,
                      if(sum (Amount)>1000 and sum (Amount_sum)<=5000,

                          '3. 1000-5000 грн.'

                      ,

                          if(sum (Amount_sum)>5000,'4.

                              более 5000 грн.'

                          )

                      )

                  )

              ) AS Bucket

          ;

          LOAD

              sum(Amount) AS Amount_sum,

              EntityId

          RESIDENT myTable

               GROUP BY EntityId;

           

           

          Regards

          Matt

            • Moving calculated dimension to script
              Miroslav Žebrák

              Thanks Matt, I am sure it will work!

               

              But I have "EntityId" in several tables.

               

              Table_AmountRank:

              LOAD RecordId,

                   [Amount TypeId],

                   [Amount Type],

                   CurrencyId,

                   Amount,

                   [Amount without conversion],

                   PaymentCount,

                   PaymentDate,

                   Day,

                   Month,

                   Year,

                   AmountRank

              FROM ...

               

               

               

              Table_CigEntity:

              LOAD EntityId,

                   [Entity Type]

              FROM ...

               

               

               

              Table_Relational:

              LOAD ContractId,

                   EntityId,

                   RelationType

              FROM ...

               

              Table_Address:

              LOAD [Address Id],

                   PostalCode,

                   AddressId,

                   [Address TypeId],

                   EntityId,

                   id,

                   [Address Type]

              FROM ...

               

              I tried to do:

               

              Join (Table_AmountRank)

              LOAD EntityId,

                   [Entity Type]

              FROM ...

               

              but QV returned system error with out of memory. I have 8GB RAM.

                • Re: Moving calculated dimension to script
                  Matthew Fryer

                  The problem lies witht he fact that your Table_AmountRank doesn't seem to have any field within it that matches a field within Table_CigEntity which you are trying to join to it. If there is no relationship in the data you can't expect QlikView to invent one for you. The out of memory is because QlikView is calculating the cartesian product of the 2 tables, all records in one relating to all records in the other, and will result in a table containing a number of records equal to the number of records in the 2 tables multiplied together.

                   

                  Does the RelationType field relate to [Amount Type] by any chance?

                • Moving calculated dimension to script
                  Miroslav Žebrák

                  Thanks you very much for explanation. I think, there is no chance.

                   

                  And about IF, which you posted before. It should not be like this? Because SUM can be used only with Group By. And we are creating Amount_sum below in resident table.

                   

                       if(Amount_sum <=200,'1. до 200 грн.',

                       if(Amount_sum >200 and Amount_sum <=1000,'2. 200-1000 грн.',

                       if(Amount_sum >1000 and  Amount_sum <=5000,'3. 1000-5000 грн.',

                       if(Amount_sum >5000,'4.более 5000 грн.')))) AS Interval,

                   

                  This is my whole script now:

                   

                  Table_AmountRank:

                  LOAD RecordId,

                       [Amount TypeId],

                       [Amount Type],

                       CurrencyId,

                       Amount,

                  //     if(Amount_sum <=200,'1. до 200 грн.',

                  //     if(Amount_sum >200 and Amount_sum <=1000,'2. 200-1000 грн.',

                  //     if(Amount_sum >1000 and  Amount_sum <=5000,'3. 1000-5000 грн.',

                  //     if(Amount_sum >5000,'4.более 5000 грн.')))) AS Interval,

                       [Amount without conversion],

                       PaymentCount,

                       PaymentDate,

                       Day,

                       Month,

                       Year,

                       AmountRank

                  FROM

                  Table_AmountRank.QVD

                  (qvd);

                   

                  //// must have joined table

                  //Interval_test:

                  //LOAD

                  //

                  //  sum(Amount) AS Amount_sum,

                  //    EntityId

                  //

                  //RESIDENT Table_AmountRank

                  //GROUP BY EntityId;

                   

                  Table_MinOverdue:

                  LOAD ContractId,

                       MinOverdueAmount,

                       OverdueNum

                  FROM

                  Table_MinOverdue.QVD

                  (qvd);

                   

                   

                  Table_Contract:

                  LOAD ContractId,

                       [Contract TypeId],

                       [Contract Type]

                  FROM

                  Table_Contract.QVD

                  (qvd);

                   

                   

                  Table_Records:

                  LOAD RecordId,

                       ContractId

                  FROM

                  Table_Records.QVD

                  (qvd);

                   

                   

                  Table_Dates:

                  LOAD RecordId,

                       [Contract Date],

                       [Date TypeId],

                       [Date Type]

                  FROM

                  Table_Dates.QVD

                  (qvd);

                   

                   

                  Table_Lookup:

                  LOAD RecordId,

                       [Contract Status]

                  FROM

                  Table_Lookup.QVD

                  (qvd);

                   

                   

                  Table_CigEntity:

                  LOAD EntityId,

                       [Entity Type]

                  FROM

                  Table_CigEntity.QVD

                  (qvd);

                   

                   

                  Table_Relational:

                  LOAD ContractId,

                       EntityId,

                       RelationType

                  FROM

                  Table_Relational.QVD

                  (qvd);

                   

                   

                  // SQL SELECT Id as LookupId,

                  //    NameNative

                  //FROM "CigSuite_v3_UA".dbo."c_ge_l_LookupValue";

                   

                  Table_Address:

                  LOAD [Address Id],

                       PostalCode,

                       AddressId,

                       [Address TypeId],

                       EntityId,

                       id,

                       [Address Type]

                  FROM

                  Table_Address.QVD

                  (qvd);

                   

                  Table_Regions:

                  LOAD ID_Regions,

                       Regions_Lable,

                       Post_index as 'PostalCode'

                  FROM

                  Ukraine_final.xls

                  (biff, embedded labels, table is Post_index$);

                    • Re: Moving calculated dimension to script
                      Matthew Fryer

                      The code I posted has 2 load statements on a single statement. This is known as a "proceeding load on a proceeding load" and allows you to do multiple stemps of data transformation.

                       

                      Imagine the below code works from the bottom up:

                       

                      LOAD

                          *,

                          if(sum (Amount_sum) <=200,

                              '1. до 200 грн.'

                          ,

                              if(sum (Amount) >200 and sum (Amount_sum)<=1000,

                                  '2. 200-1000 грн.'

                              ,
                                  if(sum (Amount)>1000 and sum (Amount_sum)<=5000,

                                      '3. 1000-5000 грн.'

                                  ,

                                      if(sum (Amount_sum)>5000,'4.

                                          более 5000 грн.'

                                      )

                                  )

                              )

                          ) AS Bucket

                      ;

                      LOAD

                          sum(Amount) AS Amount_sum,

                          EntityId

                      RESIDENT myTable

                           GROUP BY EntityId;

                       

                      The data is first retrieved from the resident table and the Amount_sum field is calculated by grouping on Entity. This is then passed up into the above load statement which uses the if functions to create the Bucket field based on the Amount_sum that has been calculated below.

                       

                      Matt