Double Join to Same Table

[#1]

Hi,

I have Four tables

ORDERS

ORDER_ID

TICKER

CLIENT_ID

SIDE

QUANTITY

TICKERS

TICKER

COMPANY

SECTOR

MATCHES

ORDER_ID

MATCHED_ORDER_ID

SAME_TICKER_MATCH

CLIENT

CLIENT_ID

CLIENT_NAME

MATCHES table contains matches b/w orders entered by various clients; The match can be on same ticker or tickers in the same sector.

Also the matches tables contains two match records for the match i.e A- B & B-A

I am trying to find out - how many matches are there for each ticker/client/company or any other attributes of orders or Ticker dimension

Also need a straight table in the format

Buy Client

Sell Client

Buy Ticker

Sell Ticker

Need to be able to filter by Client and Ticker

One solution I can think of is to introduce a primary key in the matches table in format - BUY_ORDER_ID + SELL_ORDER_ID; so the A-B & B-A legs have the same primary key; However, I am not entirely sure how to do this in the load script.

then use Only({$<SIDE= {'BUY'}>} CLIENT_NAME) as measure in the straight table but this prevents me from doing any filtering on the table. Also, this gives null values when I apply some filters

Would it be possible to have a single filter that would filter on two columns in the straight table i.e if TICKER is in either Buy or Sell then show that records.

I have attached the data and and the test app.

What is the best way to model this?

Thanks in advance!

[rohitk1609 · Accepted solution]

Hi Arvind,

I have created a sample application on your data , please have a look once and let me know what is your use case ,If you tell me what you want in Excel sheet so I can reflect it here on Sense.

[#3]

Thanks Rohit.

I am trying to build a straight table in the form

Same Ticker Match
TICKER	Buy Client	Sell Client	Buy Quantity	Sell Quantity	Sector

Diff  Ticker Match
Buy Ticker	Sell Ticker	Buy Client	Sell Client	Buy Quantity	Sell Quantity	Buy Sector	Sell Sector

then need to have a single filter for either legs of the match. For example, choosing CLIENT_1 should filter the table and show all records where CLIENT_1 appears on either side of the match essentially the answer to the question give me all the matches involving CLIENT_1.