9 Replies Latest reply: Apr 6, 2012 12:24 AM by Jagan Nalla RSS

    Help with logic

      I am trying to display a sum of quantity sold within a set of ranges, and I am not getting the results that I need. 

       

      Here are the ranges:

       

      0-11

      12-19

      20-29

      30-39

      40-79

      80+

       

      My primary data is as follows:

       

      'CAGE_Region',

      [CAGE District] as 'CAGE_District',

      [CAGE Territory] as 'CAGE_Territory',

      MakeDate(Left([GL Date],4),Mid([GL Date],5,2),Right([GL Date],2)) as 'Calendar_Date',

      [Location Number] as 'Location_Number',

      [Primary Brand] as 'Brand',

      [Invoice Extended Amount] as 'Net_Sales',

      [Carton Equivalent] as 'QVOF'

       

      I am using the following to classify the ranges within the load script, but I am not getting the unique count by location since the load script is using the calendar date.

       

      Carton_Bucket:

      LOAD

      [CAGE Region] as 'CAGE_Region',

      [CAGE District] as 'CAGE_District',

      [CAGE Territory] as 'CAGE_Territory',

      MakeDate(Left([GL Date],4),Mid([GL Date],5,2),Right([GL Date],2)) as 'Calendar_Date',

      [Primary Brand] as 'Brand',

      [Location Number] as 'Location_Number',

      If(([Carton Equivalent]<12),'<12',If(([Carton Equivalent]>=12 and [Carton Equivalent]<20),'12-19',

      If(([Carton Equivalent]>=20 and [Carton Equivalent]<30),'20-29',If(([Carton Equivalent]>=30 and [Carton Equivalent]<40),'30-39',

      If(([Carton Equivalent]>=40 and [Carton Equivalent]<80),'40-79','80+'))))) as 'Carton_Bucket'

      FROM

      [P:\US Business Operations\2012\Qlikview\Promo_Data_CSV.csv]

      (txt, unicode, embedded labels, delimiter is '\t', msq);

       

       

      Calendar Date    Location_Number   Carton Equivalent

      3/1/12                    123                             2

      3/2/12                    123                             4

      3/3/12                    123                             20

       

      The load script is generating the following for location 123 (6 cartons in the 0-11 range and 20 cartons in the 20-29 range)

       

      What I am looking for is 26 cartons total in the 20-29 range (total cartons for each location regardless of day), but I cannot figure out how to get this work correctly.

       

      Any ideas on what I am doing wrong?

        • Help with logic
          Celambarasan Adhimulam

          Hi,

               Try this

          Carton:

          LOAD

          [CAGE Region] as 'CAGE_Region',

          [CAGE District] as 'CAGE_District',

          [CAGE Territory] as 'CAGE_Territory',

          MakeDate(Left([GL Date],4),Mid([GL Date],5,2),Right([GL Date],2)) as 'Calendar_Date',

          [Primary Brand] as 'Brand',

          [Location Number] as 'Location_Number'

          FROM

          [P:\US Business Operations\2012\Qlikview\Promo_Data_CSV.csv]

          (txt, unicode, embedded labels, delimiter is '\t', msq);

           

          Carton_Bucket:

          Load

          Location_Number,

          If((Sum([Carton Equivalent])<12),'<12',If((Sum([Carton Equivalent])>=12 and Sum([Carton Equivalent])<20),'12-19',

          If((Sum([Carton Equivalent])>=20 and Sum([Carton Equivalent])<30),'20-29',If((Sum([Carton Equivalent])>=30 and Sum([Carton Equivalent])<40),'30-39',

          If((Sum([Carton Equivalent])>=40 and Sum([Carton Equivalent])<80),'40-79','80+'))))) as 'Carton_Bucket'

          Resident

               Carton

          Group by Location_Number;

           

          Celambarasan

            • Help with logic

              I got the following Script Error?

               

              Table not found

              Carton_Bucket:

              LOAD

              Location_Number,

              If((Sum([Carton Equivalent])<12),'<12',If((Sum([Carton Equivalent])>=12 and Sum([Carton Equivalent])<20),'12-19',

              If((Sum([Carton Equivalent])>=20 and Sum([Carton Equivalent])<30),'20-29',If((Sum([Carton Equivalent])>=30 and Sum([Carton Equivalent])<40),'30-39',

              If((Sum([Carton Equivalent])>=40 and Sum([Carton Equivalent])<80),'40-79','80+'))))) as 'Carton_Bucket'

              Resident

                   Carton

              Group by Location_Number

              • Re: Help with logic

                I got the following Script Error?

                 

                Table not found

                Carton_Bucket:

                LOAD

                Location_Number,

                If((Sum([Carton Equivalent])<12),'<12',If((Sum([Carton Equivalent])>=12 and Sum([Carton Equivalent])<20),'12-19',

                If((Sum([Carton Equivalent])>=20 and Sum([Carton Equivalent])<30),'20-29',If((Sum([Carton Equivalent])>=30 and Sum([Carton Equivalent])<40),'30-39',

                If((Sum([Carton Equivalent])>=40 and Sum([Carton Equivalent])<80),'40-79','80+'))))) as 'Carton_Bucket'

                Resident

                     Carton

                Group by Location_Number

                  • Help with logic
                    Celambarasan Adhimulam

                    Hi,

                         Have you replaced the Carton_Bucket with my script(Carton and Carton_Bucket tables)

                     

                    Celambarasan

                      • Re: Help with logic

                        yes, I did and I got that error message.

                         

                        I was thinking, can I accomplish this in a calculated dimension?  I would like to user to be able to click through the dimensions (brand, region, etc) and view the carton bucket in the graph.  Does that give more flexibility than doing it through the load script?

                        • Re: Help with logic

                          yes, I did and I got that error message.

                           

                          I was thinking, can I accomplish this in a calculated dimension?  I would like to user to be able to click through the dimensions (brand, region, etc) and view the carton bucket in the graph.  Does that give more flexibility than doing it through the load script?

                            • Help with logic
                              Jagan Nalla

                              Hello,

                               

                              you should make group by[Carton Equivalent] and ur missing this field in main table.

                               

                               

                               

                              Carton:

                              LOAD

                              [CAGE Region] as 'CAGE_Region',

                              [CAGE District] as 'CAGE_District',

                              [CAGE Territory] as 'CAGE_Territory',

                              MakeDate(Left([GL Date],4),Mid([GL Date],5,2),Right([GL Date],2)) as 'Calendar_Date',

                              [Primary Brand] as 'Brand',

                              [Location Number] as 'Location_Number',

                              [Carton Equivalent]

                              FROM

                              [P:\US Business Operations\2012\Qlikview\Promo_Data_CSV.csv]

                              (txt, unicode, embedded labels, delimiter is '\t', msq);


                              Carton_Bucket:

                              Load

                              [Carton Equivalent],

                              If((Sum([Carton Equivalent])<12),'<12',If((Sum([Carton Equivalent])>=12 and Sum([Carton Equivalent])<20),'12-19',

                              If((Sum([Carton Equivalent])>=20 and Sum([Carton Equivalent])<30),'20-29',If((Sum([Carton Equivalent])>=30 and Sum([Carton Equivalent])<40),'30-39',

                              If((Sum([Carton Equivalent])>=40 and Sum([Carton Equivalent])<80),'40-79','80+'))))) as 'Carton_Bucket'

                              Resident

                              Carton

                              Group by [Carton Equivalent];

                              hope it helps u.

                      • Help with logic
                        Jagan Nalla

                        Try like this it may helps u..

                         

                        Temp:

                        LOAD

                        RowNo() as CartonEquivalent

                        AutoGenerate 100;

                         

                        ResTemp:

                        LOAD CartonEquivalent as M1, '0-11' as Range

                        Resident Temp

                        Where CartonEquivalent <12;

                        Concatenate

                        LOAD CartonEquivalent as M1, '12-19' as Range

                        Resident Temp

                        Where CartonEquivalent <20

                        and not Exists(M1,CartonEquivalent)

                        ;

                        Concatenate

                        LOAD CartonEquivalent as M1, '20-29' as Range

                        Resident Temp

                        Where CartonEquivalent <30

                        and not Exists(M1,CartonEquivalent)

                        ;

                        Concatenate

                        LOAD CartonEquivalent as M1, '30-39' as Range

                        Resident Temp

                        Where CartonEquivalent <40

                        and not Exists(M1,CartonEquivalent)

                        ;

                        Concatenate

                        LOAD CartonEquivalent as M1, '40-79' as Range

                        Resident Temp

                        Where CartonEquivalent <80

                        and not Exists(M1,CartonEquivalent)

                        ;

                        Concatenate

                        LOAD CartonEquivalent as M1, '80+' as Range

                        Resident Temp

                        Where CartonEquivalent >=80;

                         

                        Map1:

                        Mapping

                        LOAD M1,Range

                        Resident ResTemp;

                         

                        DROP Table ResTemp;

                         

                        Temp2:

                        LOAD ApplyMap ('Map1', CartonEquivalent ) as Range1,CartonEquivalent

                        Resident Temp;

                         

                        DROP Table Temp;

                          • Help with logic
                            Jagan Nalla

                            If we convert according to your code. it will look like this

                             

                             

                            Temp:

                            LOAD

                            CartonEquivalent

                            FROM

                            [P:\US Business Operations\2012\Qlikview\Promo_Data_CSV.csv]

                            (txt, unicode, embedded labels, delimiter is '\t', msq);

                             

                            ResTemp:

                            LOAD CartonEquivalent as M1, '0-11' as R1

                            Resident Temp

                            Where CartonEquivalent <12;

                            Concatenate

                            LOAD CartonEquivalent as M1, '12-19' as R1

                            Resident Temp

                            Where CartonEquivalent <20

                            and not Exists(M1,CartonEquivalent)

                            ;

                            Concatenate

                            LOAD CartonEquivalent as M1, '20-29' as R1

                            Resident Temp

                            Where CartonEquivalent <30

                            and not Exists(M1,CartonEquivalent)

                            ;

                            Concatenate

                            LOAD CartonEquivalent as M1, '30-39' as R1

                            Resident Temp

                            Where CartonEquivalent <40

                            and not Exists(M1,CartonEquivalent)

                            ;

                            Concatenate

                            LOAD CartonEquivalent as M1, '40-79' as R1

                            Resident Temp

                            Where CartonEquivalent <80

                            and not Exists(M1,CartonEquivalent)

                            ;

                            Concatenate

                            LOAD CartonEquivalent as M1, '80+' as R1

                            Resident Temp

                            Where CartonEquivalent >=80;

                             

                            Map1:

                            Mapping

                            LOAD M1,R1

                            Resident ResTemp;

                             

                            DROP Table ResTemp,Temp;

                             

                            MainTable:

                            LOAD

                            [CAGE Region] as 'CAGE_Region',

                            [CAGE District] as 'CAGE_District',

                            [CAGE Territory] as 'CAGE_Territory',

                            MakeDate(Left([GL Date],4),Mid([GL Date],5,2),Right([GL Date],2)) as 'Calendar_Date',

                            [Primary Brand] as 'Brand',

                            [Location Number] as 'Location_Number',

                            ApplyMap ('Map1', CartonEquivalent ) as Range

                            FROM

                            [P:\US Business Operations\2012\Qlikview\Promo_Data_CSV.csv]

                            (txt, unicode, embedded labels, delimiter is '\t', msq);