Rank and Extract

[rahulsingh12]

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.

[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]

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)

[rahulsingh12]

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]

No Sunny,

I dont even want to load any such data.

Regards,

Rahul

[sunny_talwar]

Can you please share MUV_Logic.xlsx

[rahulsingh12]

Hi,

please find the file