Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
See lines 03 an 10 below:
join(Customers)
load [Customer ID], date(min([Transaction Date])) as [First Deposit Date],
FirstSortedValue([Transaction Amount],[Transaction Date]) as [First Deposit Amount]
Resident Transactions
where [Transaction Type] ='Deposit'
group by [Customer ID];
join(Customers)
load [Customer ID], date(min([Transaction Date])) as [First Withdrawal Date],
FirstSortedValue([Transaction Amount],[Transaction Date]) as [First Withdrawal Amount]
Resident Transactions
where [Transaction Type] ='Withdrawal'
group by [Customer ID];
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;
Dear G Wassenaar,
Working like a charm…Thanks for the same... You made my day!!
One more question… I know you definitely have an answer… what if I also want the first Deposit and Withdrawal amount also…
See lines 03 an 10 below:
join(Customers)
load [Customer ID], date(min([Transaction Date])) as [First Deposit Date],
FirstSortedValue([Transaction Amount],[Transaction Date]) as [First Deposit Amount]
Resident Transactions
where [Transaction Type] ='Deposit'
group by [Customer ID];
join(Customers)
load [Customer ID], date(min([Transaction Date])) as [First Withdrawal Date],
FirstSortedValue([Transaction Amount],[Transaction Date]) as [First Withdrawal Amount]
Resident Transactions
where [Transaction Type] ='Withdrawal'
group by [Customer ID];
Dear gwassenaar
Even though my requirements fulfilled by above script.... just want to know whether we can achieve the same using IF in script?
Thanks in advance.
Can anyone reply on this?