2 Replies Latest reply: Sep 26, 2016 6:01 PM by Aravind Sasidharan RSS

    Double Join to Same Table

    Aravind Sasidharan



      I have Four tables



















      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!

        • Re: Double Join to Same Table
          rohit kumar

          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.

            • Re: Double Join to Same Table
              Aravind Sasidharan

              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.