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