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: 
rahulsingh12
Contributor III
Contributor III

Rank and Extract

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.

8 Replies
sunny_talwar

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?

rahulsingh12
Contributor III
Contributor III
Author

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

sunny_talwar

If your data is correctly sorted you can try this

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


Capture.PNG

rahulsingh12
Contributor III
Contributor III
Author

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

sunny_talwar

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

rahulsingh12
Contributor III
Contributor III
Author

No Sunny,

I dont even want to load any such data.

Regards,

Rahul

sunny_talwar

Can you please share MUV_Logic.xlsx

rahulsingh12
Contributor III
Contributor III
Author

Hi,

please find the file