topic Re: Rank and Extract in QlikView

Rank and Extract

rahulsingh12 — Tue, 31 Jul 2018 06:55:59 GMT

Hi I have two columns with the respective data set;

COLA COLB

x 10

x 20

x 30

x 3

x 0

x -4

x -2

x -1

y 10

y 11

y 0

y 1

The above data is sorted on some pre-defined order.

How can I extract only the values more than equal to the largest negative value in colB

i.e all the values >=-4 in col B for ColA X and and all the values>0(i.e 10,11,0 not 1) for ColA Y value.

Re: Rank and Extract

sunny_talwar — Tue, 31 Jul 2018 11:45:48 GMT

Not sure I understand the required output... would be able to provide the expected output as a table to see what you are looking to get?

Re: Rank and Extract

rahulsingh12 — Wed, 01 Aug 2018 06:38:12 GMT

Hi Sunny,

Attaching the application:

if you see in the chart made the data is sorted in some order.

For each stockcode I want to pick all the data till the LeftOver Column becomes zero or less than the QTTY.

Thus for 110011 we should get data from Rank 6 to 11

For 1122 we should get the data from Rank 1 to 5

Let me know if any doubts on the same.

Thanks

Re: Rank and Extract

sunny_talwar — Wed, 01 Aug 2018 11:24:37 GMT

If your data is correctly sorted you can try this

=Aggr(If(Alt(Above(LEFTOVER), 1) > 0, RANK), COMPANY, STOCKCODE, RANK)

Re: Rank and Extract

rahulsingh12 — Fri, 03 Aug 2018 05:16:14 GMT

Hi Sunny,

Thanks for the reply.

But can we do it in Backend script so that I can store the result in QVD.

Regards,

Rahul

Re: Rank and Extract

sunny_talwar — Fri, 03 Aug 2018 10:55:29 GMT

So, you don't want to load any data which don't meet the condition or do you want to flag them?

Re: Rank and Extract

rahulsingh12 — Fri, 03 Aug 2018 11:05:14 GMT

No Sunny,

I dont even want to load any such data.

Regards,

Rahul

Re: Rank and Extract

sunny_talwar — Fri, 03 Aug 2018 11:10:08 GMT

Can you please share MUV_Logic.xlsx

Re: Rank and Extract

rahulsingh12 — Fri, 03 Aug 2018 11:17:27 GMT

Hi,

please find the file