Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

MVP
MVP

First Date in SCRIPT

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?

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Re: First Date in SCRIPT

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


talk is cheap, supply exceeds demand
5 Replies
MVP & Luminary
MVP & Luminary

Re: First Date in SCRIPT

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;


talk is cheap, supply exceeds demand
MVP
MVP

Re: First Date in SCRIPT

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…

MVP & Luminary
MVP & Luminary

Re: Re: First Date in SCRIPT

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


talk is cheap, supply exceeds demand
MVP
MVP

Re: First Date in SCRIPT

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.

MVP
MVP

Re: First Date in SCRIPT

Can anyone reply on this?