2 Replies Latest reply: Nov 26, 2013 4:55 AM by Paul Edrich RSS

    Finding 2 max dates in two tables

    Paul Edrich

      Hi

       

      I have two  transaction tables with customer IDs, One table is Buy & the other is Sell Field names are CustomerBuyId & CustomerSellId both tables have the Field Transactiondate .

       

      I would like to determine if a customer who buys also returns to sell at a later date - there maybe a really simple way to script this as a flag - I have tried:-

       

       

      LOAD DISTINCT
      CustomerSellID,
      Max(TransactionDate)as LastSellDate
      RESIDENT Sells
      GROUP BY CustomerSellID;

      JOIN (Buys) LOAD DISTINCT
      CustomerBuyID
      Max(TransactionDate)
      RESIDENT tmp;
      DROP TABLE tmp;
      LEFT JOIN (Buys) LOAD DISTINCT
      LastSelldate,
      CustomerBuyId
      Max(TransactionDate),
      if(CustomerBuyID=CustomerSellID,'Yes','No') as ReturningCustomer
      RESIDENT Buys;

      Field not found errors happen on step through - any help or alternative solutions would be a great help.

       

      Many thanks