cancel
Showing results for 
Search instead for 
Did you mean: 

I need to get the top 10 products from a filtered table

former_member680965
Participant
0 Kudos

I have initial raw data that contains columns for trader, counterparty, product, mtm abs. From this data, I have to pick out the top 10 counterparties. So I get the top 10 counterparties using this variable:

Top 10 cp =Rank([mtm abs];[Client Valuations Counterparty])

and then filtering that table such that Top 10 cp less than or equal to 10.

Now with this table, I then need to get the top 10 products. I cannot use the initial raw data. The rule is to use the table you get when you filter the initial raw data by the top 10 counterparty.

I tried these formulas:

  • -Top 10 Product = Rank([mtm abs];[Client Valuations Product Class Orig]) Where([Top 10 CP] <= 10) - does not give the right result as it basis it on the initial raw data
  • -Top 10 Product = Rank([mtm abs];[Client Valuations Product Class Orig]) Where ([Client Valuations Counterparty]) InList (“NBCGF” ; ”ICECLEAR”; ”MSIL”; ”DZBANK” ; “MSCS” ; “HSBCEU” ; “BNPP” ; “LCH” ; “GSB” ; “NWMLI”) - I wish I could do this but the top 10 counterparties have been hardcoded, next time you run the report for a different date, the top 10 cp can change

Really am struggling and I so wish someone can help please.

Thanks,

Jay

Accepted Solutions (0)

Answers (7)

Answers (7)

former_member680965
Participant
0 Kudos

It’s unfortunate I cannot upload actual data on here. I saved it in a .txt file and tried to upload the file but it’s complaining saying file format is not valid. I thought we were able to upload txt files. I applied your logic to this dataset and it’s not giving the desired results.

ayman_salem
Active Contributor
0 Kudos

Uploading files other than images is not permitted.

Also, it's better to add a comment rather than adding an "Answer" every time.

former_member680965
Participant
0 Kudos

Yes, this is the order I have applied as well.
Are you sure R2 focuses on the data that has top 10 couterparties and not all counterparties? Remember, R2 is to get the top 10 products where Counterparty is in top 10.

ayman_salem
Active Contributor
0 Kudos

In my example above (according to your dummy data) it works and the result is the same as what you mentioned in your screenshot.

Try applying the first filter R1 and look for the result, then add the R2 filter and check again.

former_member680965
Participant
0 Kudos

Yes, I have applied those filters to the table and it is still incorrect. On my actual dataset, the top 10 product should show SWPB but it is not and instead showing some other value which should not be in the top 10.

ayman_salem
Active Contributor
0 Kudos

Note that the order of the filters is very important (R1 first, then R2).

former_member680965
Participant
0 Kudos

OK thank you for your time and effort, but this does not work as expected on my actual dataset.

This works R1. =Rank(Sum([abs mtm diff]); [countrerparty])

But this is wrong R2 =Rank(Sum([abs mtm diff]);[product]) because you haven't restricted this to be applicable only to the top 10 counterparties. Hence why I did say in my original post I need to do something like this

-Top 10 Product = Rank([mtm abs];[Client Valuations Product Class Orig]) Where ([Client Valuations Counterparty]) InList (“NBCGF” ; ”ICECLEAR”; ”MSIL”; ”DZBANK” ; “MSCS” ; “HSBCEU” ; “BNPP” ; “LCH” ; “GSB” ; “NWMLI”) - I wish I could do this but the top 10 counterparties have been hardcoded, next time you run the report for a different date, the top 10 cp can change

ayman_salem
Active Contributor
0 Kudos

If you filter the table as previously described, you will get the result you want.

ayman_salem
Active Contributor
0 Kudos

Proceed as follows:

* Define the following variables:

R1. =Rank(Sum([abs mtm diff]); [countrerparty])

R2: =Rank(Sum([abs mtm diff]);[product])

* Filter the table for "R1 <= 10 and R2 <= 10"

....

hopefully that helps

former_member680965
Participant
0 Kudos

Please see the attached images in that order to see the steps of what needs to be done and to get a better understanding of my problem. Thank you

capture.jpg

capture2.jpg

capture3.jpg

capture4.jpg

ayman_salem
Active Contributor
0 Kudos

It is not clear what you want.

You would like to receive TOP 10 with the value <= 10 from raw data?

or

you need value <= 10 from the TOP 10?

Can you attach an example of raw data and a table for the final result?

former_member680965
Participant
0 Kudos

Hi, I will attach example..

thanks