4 Replies Latest reply on Nov 30, 2017 6:50 PM by John Lee

    Number of records based on 2 different criteria

    John Lee

      I haven't been able to quite figure this out... (part of this might belong in the data sources and connections section)

       

       

      I have 2 tables in a DB:

       

      table A (contains account info)

       

      id, name, country, province

      1,123 motors, canada, ontario

      2,234 motors, canada, quebec

       

       

      table B (contains transaction info)

      this contains things like:  item_id, seller_id, buyer_id, date_listed, date_sold

      *** note, that both seller_id AND buyer_id refer to a row in tableA ***

       

      itemid,seller_id,date_listed,date_sold,buyer_id

      100,1,2017-11-10,NULL,NULL

      101,1,2017-11-10,2017-11-11,2

      102,2,2017-11-14,2017-11-15,1

       

      i've currently got these 2 tables joined on the following (in Data Sources) as a left join:

      tableA left join tableB on:

      tableA.id = tableB.seller_id

      tableA.id = tableB.buyer_id

      (i'm not sure if this does what i think it does)

       

       

      what i need (to start) is a crosstab that would show:

       

      tableA.name  |  [# of records] where tableA.name is the seller  |  [# of records] where tableA.name is the buyer

      123 Motors  |  4 (sold)  |  2 (bought)

      234 Motors  |  0 (sold)  |  50 (bought)

       

       

      all of the other dimensions can be used to create filters and parameters (i.e.  date_range, country... etc).

       

       

      am i even approaching this correctly?  any help would be much appreciated.

        • 1. Re: Number of records based on 2 different criteria
          Tom W

          I don't think your join is right for starters.

          You're effectively saying: "For each record in Table A join it to a corresponding record in Table B where the ID on TableA = the Seller ID on Table B AND it also equals the BuyerID on the same record in Table B.

           

          For example, if your id in Tablea A was 1 and you had two records in Table B:

           

          BuyerIdSellerIdResult
          11This would join
          12This would not join

           

           

          Instead, what you need to do is left join A on the ID to B on the buyer ID and call this table 'Buyer' then left join A on the ID to B on the seller ID and call this table 'Seller'

           

           

           

          Then create a calc like;

          COUNTD([SaleId]) to get the number of buying instances.

           

          and

          COUNTD([SaleId (Seller)]) to get the number of selling instances.

          • 2. Re: Number of records based on 2 different criteria
            John Lee

            thanks for the quick reply!  im a little confused (by your table names)...  I think i understand though:

            (remember, tableA has account info, and tableB has transaction info)

            so, basically i'm joining twice, but once on the seller id and once on the buyer id.

             

            my only problem is that tableB is HUGE, so i don't want to load and join that thing twice.  would it work if:

            - i 'load' tableA and call it tableA1 (for the seller account side)

            - left join tableB on tableA1.id = tableB.seller

            Screen Shot 2017-11-30 at 9.49.03 PM.png

            - i load tableA again and calle it tableA2 (for the buyer)

            - right join tableA2 to tableB on tableA2.id = tableB.buyer

            Screen Shot 2017-11-30 at 9.49.19 PM.png

            (I want to use left and right joins b/c i want to show any account that has zero sales or buys)

             

            I basically want the full list of accounts;  if i have 1000 accounts i want 1000 rows (showing zeros for # sold and # bought)

             

            will this setup work?

            • 3. Re: Number of records based on 2 different criteria
              Tom W

              Your proposed join won't work because then you'll end up with two 'master' list of accounts.

               

              If you're connecting to a SQL server, use a custom SQL connection and you can define a connection which is effectively an OR.

               

              SELECT a,b,c

              FROM TableA

              LEFT JOIN TableB on TableA.Id = TableB.BuyerId or TableA.Id = TableB.SellerId

               

              If you're using Excel, take a look in the help for using the legacy Excel connector and you can also do custom SQL in that to the same effect as above.