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: 
Not applicable

Double Join to Same Table

Hi,

I have Four tables

ORDERS

 

ORDER_IDTICKERCLIENT_IDSIDEQUANTITY

TICKERS

  

TICKERCOMPANYSECTOR

MATCHES

 

ORDER_IDMATCHED_ORDER_IDSAME_TICKER_MATCH

CLIENT

CLIENT_IDCLIENT_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 ClientSell ClientBuy TickerSell 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!

1 Solution

Accepted Solutions
rohitk1609
Master
Master

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.

View solution in original post

2 Replies
rohitk1609
Master
Master

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.

Not applicable
Author

Thanks Rohit.

I am trying to build a straight table in the form

    

Same Ticker Match
TICKERBuy ClientSell ClientBuy QuantitySell QuantitySector

    

Diff  Ticker Match
Buy TickerSell TickerBuy ClientSell ClientBuy QuantitySell QuantityBuy 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.