Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to add additional columns in QlikView

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
1 Solution

Accepted Solutions
sunny_talwar

You can look into partial reload for archiving within QlikView. Although it is possible to do partial reload on servers, it would require some configurations which I am not full sure about. The idea behind partial reload is that, you just reload the new data while still keeping the existing data. This fits your need, but like I said, can be complicated.

On the other hand, you don't have to keep a single file for each day. You can timestamp your data and save them all in one single qvd. The process is called incremental reload and you will find 100 of discussions and blogs on this topic

Incremental Load in Qlikview - Sources

View solution in original post

17 Replies
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

Not applicable
Author

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
sunny_talwar

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

Not applicable
Author

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

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;

Not applicable
Author

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 ?

sunny_talwar

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

Not applicable
Author

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.

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?