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

    Double Join to Same Table

    Aravind Sasidharan

      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!

        • 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.