First Date in SCRIPT

[MK_QSL]

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?

[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;

[MK_QSL]

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…

[Gysbert_Wassenaar · Accepted solution]

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];

[MK_QSL]

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.

[MK_QSL]

Can anyone reply on this?