17 Replies Latest reply: Apr 7, 2017 6:07 AM by Prashant k RSS

    How to add additional columns in QlikView

    Prashant k

      How can i add the additional column Rank and add the ranking logic in Qlikview.

       

      i have a table as below with (Region ,Supplier Name & Average weights) , I need to rank the suppliers region wise  .So based on average weight, i rank a supplier only specific to that region . So every region would have its separate ranking based on weights.

       

         

      RegionSupplier NameAverage weights
      (Weight*KPI's)
      Rank region wise
      APACxyz17.751
      APACxyz27.52
      APACxyz35.73
      Europeabc44.41
      Americasghi56.31
      Americasghi66.052
        • Re: How to add additional columns in QlikView
          Sunny Talwar

          May be like this

           

          Table:

          LOAD * INLINE [

              Region, Supplier Name, Average weights

              APAC, xyz1, 7.75

              APAC, xyz2, 7.5

              APAC, xyz3, 5.7

              Europe, abc4, 4.4

              Americas, ghi5, 6.3

              Americas, ghi6, 6.05

          ];

           

          FinalTable:

          LOAD *,

            If(Region = Previous(Region), RangeSum(Peek('Rank'), 1), 1) as Rank

          Resident Table

          Order By Region, [Average weights] desc;

           

          DROP Table Table;

           

          Capture.PNG

            • Re: How to add additional columns in QlikView
              Prashant k

              Thanks Sunny this works.

              I am new to QlikView . So ,One question ,how can i do multiplication between columns

               

              Example : My Average weight is multiplication of (KPI * Weight %) , So my rank and Average weights is the output columns.

               

              RegionSupplier NameKPI Weight %Average weights (KPI *Weight%)Rank region wise
              APACxyz110010%7.751
              APACxyz21005%7.52
              APACxyz310010%5.73
              Europeabc4807%4.41
              Americasghi510010%6.31
              Americasghi610010%6.052
                • Re: How to add additional columns in QlikView
                  Sunny Talwar

                  How do you get 7.75 by multiplying 100 with 10%?

                    • Re: How to add additional columns in QlikView
                      Prashant k

                      Just an example ..ignore 7.75 ..the number to be populated in average weight should be %weight*KPI and then be ranked.

                        • Re: How to add additional columns in QlikView
                          Sunny Talwar

                          May be this

                           

                          Table:

                          LOAD *,

                            KPI * [Weight %] as [Average weights];

                          LOAD * INLINE [

                              Region, Supplier Name, KPI, Weight %

                              APAC, xyz1, 100, 10%

                              APAC, xyz2, 100, 5%

                              APAC, xyz3, 100, 10%

                              Europe, abc4, 80, 7%

                              Americas, ghi5, 100, 10%

                              Americas, ghi6, 100, 10%

                          ];

                           

                          FinalTable:

                          LOAD *,

                            If(Region = Previous(Region), RangeSum(Peek('Rank'), 1), 1) as Rank

                          Resident Table

                          Order By Region, [Average weights] desc;

                           

                          DROP Table Table;

                            • Re: How to add additional columns in QlikView
                              Prashant k

                              Thanks this work.

                               

                              I am using a excel sheet to import Region, Supplier name, KPI and Weight % in my table . I have to create this table every month which can have additional suppliers names and different Weight % .

                              How can i keep my previous month table data every month and go on adding new table with new details (new supplier name , weight %)

                              So that i can compare every month the supplier rankings ?

                                • Re: How to add additional columns in QlikView
                                  Sunny Talwar

                                  Create multiple qvds for each day and timestamp them so that you can compare days

                                    • Re: How to add additional columns in QlikView
                                      Prashant k

                                      Didn't get you .. may i elaborate  more..

                                       

                                      I am currently doing a set of calculations to arrive on a final table (Table 1)

                                      Table 1 is  created on month 1 to arrive on supplier rank 1 .

                                      Now , i have to use the same script as i used for creating table 1 to create table 2 for month 2 and arrive on rank 2 for second month .

                                      Every month , Table 2 data get added to Table 1 to create a archive table and new data get loaded in Table 2 .

                                      and i have to compare the data of current and previous ranks .

                                       

                                      Month 1Month 2Month 3Month 4
                                      Current Data TableTable 1Table 2Table 3Table 4
                                      Archive Table Table 1+Table 2Table 1+Table 2+Table 3
                                      Output column (Supplier Rank)Rank 1Rank 2Rank 3Rank 4
                                      Compare Rank Rank 2 vs Rank1Rank 1 vs Rank 2 vs Rank 3Rank 1 vs Rank 2 vs Rank 3 vs Rank 4

                                       

                                      I don't want to maintain archive qvd's to upload every month for comparison . Is it possible to keep a table where

                                      i can append data month on month and when comparison is required is can compare any month rank.

                                        • Re: How to add additional columns in QlikView
                                          Sunny Talwar

                                          When you reload your application, it reloads all the data (unless you are doing a partial reload). So, I am not sure how you plan to apend data month on month? Or may be I am still not sure what you are looking to get.... Taking a step back, this is table with the rank is needed in the script or you are looking to create this on the front end?

                                            • Re: How to add additional columns in QlikView
                                              Prashant k

                                              Ok ..let me explain

                                               

                                              1. I have excel  sheet template with columns Region, Supplier name , KPI (in number ), Weight % (in %).

                                              2. I export this sheet in qlikview and add two additional columns 1. Average Weight (KPI *Weight %) and  2. Rank (which is region wise supplier rank ) .

                                              3. Now , for example consider this is my April data.

                                               

                                              4. Next month i.e in May i will float the same excel sheet template and fetch new details , now my new details would be same set of supplier or new supplier added and changed KPI (in number ), Weight % (in %) .

                                              5. Now i have to repeat the step 2 to arrive on new supplier rank for May.

                                              6.Now i will do a comparison for May rank and April rank .

                                               

                                              Now this goes on every month and i keep archiving my every month supplier data in a table and do current and previous month supplier rank comparison.

                                               

                                              Hope i am clear now.

                                                • Re: How to add additional columns in QlikView
                                                  Sunny Talwar

                                                  Now this goes on every month and i keep archiving my every month supplier data in a table and do current and previous month supplier rank comparison.

                                                  What I am confused about is the fact that you mention that you keep archiving this every month, but you don't want to archive this in a qvd? So, you plan to load all the Excel files everytime you reload the app, or somehow you want the application to archive this information for you?

                                                    • Re: How to add additional columns in QlikView
                                                      Prashant k

                                                      I want qlikview to archive this information for me.

                                                       

                                                      In case qlikview cant do this....Just want to check the possibility to archive the every month qvd in single file... but i don't want to create  qvd for each month separately and load every month qvds for everytime i do comparison. For example : i have my april data qvd , then in month of may when i archive i should have the arpil month and may month data in a single qvd . So every time i just have to load a single archived qvd and the current month new table .