5 Replies Latest reply: Oct 26, 2013 7:43 AM by Manish Kachhia RSS

    First Date in SCRIPT

    Manish Kachhia

      I have below Customer table….

       

      Customer ID

      Account Opening Date

      L0012121

      10/02/2013

      L0012123

      01/08/2013

      L0012234

      10/03/2013

       

       

      And Transaction Table

       

      Customer ID

      Transaction Type

      Transaction Date

      Transaction Amount

      L0012121

      Deposit

      15/02/2013

      100

      L0012121

      Deposit

      14/03/2013

      100

      L0012234

      Deposit

      14/03/2013

      100

      L0012234

      Deposit

      15/04/2013

      100

      L0012234

      Deposit

      16/04/2013

      100

      L0012234

      Withdrawal

      20/05/2013

      50

      L0012234

      Withdrawal

      25/05/2013

      50

      L0012234

      Withdrawal

      30/05/2013

      200

      L0012121

      Withdrawal

      20/08/2013

      50

      L0012123

      Deposit

      25/08/2013

      500

      L0012123

      Withdrawal

      30/08/2013

      300

      L0012234

      Deposit

      14/10/2013

      300

      L0012121

      Deposit

      15/10/2013

      100

      L0012121

      Withdrawal

      16/10/2013

      50

      L0012234

      withdrawal

      16/10/2013

      200

      L0012234

      Deposit

      18/10/2013

      100

      L0012121

      Withdrawal

      20/10/2013

      50

      L0012123

      Withdrawal

      20/10/2013

      100

      L0012123

      Deposit

      21/10/2013

      50

       

      I have created a Customer QVD and Transaction QVD using incremental load

       

      I want to insert First Deposit Date and First Withdrawal Date of Each Customer in Customer QVD.

       

      Let me know how to do this?

        • Re: First Date in SCRIPT
          Gysbert Wassenaar

          For instance like this:

          Customers:
          LOAD [Customer ID], [Account Opening Date]
          from Customer.qvd (qvd);
          
          Transactions:
          LOAD  [Customer ID], [Transaction Type], [Transaction Date], [Transaction Amount]
          from Transactions.qvd (qvd);
          
          join(Customers)
          load [Customer ID], date(min([Transaction Date])) as [First Deposit Date]
          from Transactions.qvd (qvd);
          where [Transaction Type] ='Deposit'
          group by [Customer ID];
          
          join(Customers)
          load [Customer ID], date(min([Transaction Date])) as [First Withdrawal Date]
          from Transactions.qvd (qvd);
          where [Transaction Type] ='Withdrawal'
          group by [Customer ID];
          
          STORE Customers into Customers.qvd;