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

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.

 Region Supplier Name Average weights(Weight*KPI's) Rank region wise APAC xyz1 7.75 1 APAC xyz2 7.5 2 APAC xyz3 5.7 3 Europe abc4 4.4 1 Americas ghi5 6.3 1 Americas ghi6 6.05 2

May be like this

Table:

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:

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

Resident Table

Order By Region, [Average weights] desc;

DROP Table Table;

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.

 Region Supplier Name KPI Weight % Average weights (KPI *Weight%) Rank region wise APAC xyz1 100 10% 7.75 1 APAC xyz2 100 5% 7.5 2 APAC xyz3 100 10% 5.7 3 Europe abc4 80 7% 4.4 1 Americas ghi5 100 10% 6.3 1 Americas ghi6 100 10% 6.05 2

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

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

May be this

Table:

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

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:

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

Resident Table

Order By Region, [Average weights] desc;

DROP Table Table;

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 ?

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

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 1 Month 2 Month 3 Month 4 Current Data Table Table 1 Table 2 Table 3 Table 4 Archive Table Table 1+Table 2 Table 1+Table 2+Table 3 Output column (Supplier Rank) Rank 1 Rank 2 Rank 3 Rank 4 Compare Rank Rank 2 vs Rank1 Rank 1 vs Rank 2 vs Rank 3 Rank 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.

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?

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.

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?

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 .

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

Ok Thanks . Willcheck

I tried doing incremental load , but the syntax in the attached not working. concatenate not working.

Qvd file 1 - Master QVD file .

Qvd file 2 - Increment QVD file . ( output table has same table structure as in qvd file 1 )

How should i insert records of qvd file 2 in qvd file 1.

syntax in the attached not working. concatenate not working.

attached? I don't see anything attached?

i meant the attachment in the links you gave.

I have tried linking the qvd files using the Data tab "Qlikview files" . Temporary solves my case.

But this is more of a manual task where i have go in the script and click on QlikView File and link it to my existing script. How can i automate it..So that i just run every month one script and this script add my data in my master qvd file .

So ,

Master.qvw   is with my month 1 data.

Increment.qvw script produces the  output table which has same table structure as in Master.qvw but for month 2

I want to add certain code in Increment.qvw script which can add its month 2 data in Master.qvw file.