topic Re: How to get the first Num in QlikView

How to get the first Num

Fri, 12 Jul 2013 14:56:02 GMT

Hi All,

I have the following sample:

CustomerId, VisitDate, IsBuy

1, 2013-1-1, 0

1, 2013-2-1, 0

1, 2013-3-1, 1

1, 2013-4-1, 1

2, 2013-1-1, 1

3, 2013-5-1, 0

3, 2013-6-1, 1

You can see those kind of records are the attended records for the Customers Visiting. And IsBuy shows whether customer buys something, 1 means bought something.

What I want to get is: Add one more column for that table, and show whether this record is the first bought record for the each CustomerId.

Take an example: for the Customer 1, the first bought record is the row 3.

So the result is:

CustomerId, VisitDate, IsBuy, FirstRecord

1, 2013-1-1, 0 , No

1, 2013-2-1, 0 , No

1, 2013-3-1, 1 , Yes

1, 2013-4-1, 1, No

2, 2013-1-1, 1, Yes

3, 2013-5-1, 0, No

3, 2013-6-1, 1, Yes

Can we do that in the QlikView loading script?

Thanks.

Re: How to get the first Num

swuehl — Fri, 12 Jul 2013 15:18:52 GMT

Maybe like this:

INPUT:

LOAD * INLINE [

CustomerId, VisitDate, IsBuy

1, 2013-1-1, 0

1, 2013-2-1, 0

1, 2013-3-1, 1

1, 2013-4-1, 1

2, 2013-1-1, 1

3, 2013-5-1, 0

3, 2013-6-1, 1

];

LEFT JOIN (INPUT)

LOAD CustomerId,

IsBuy,

FirstValue(if(IsBuy=1,VisitDate)) as VisitDate,

IsBuy as FirstBuy

Resident INPUT

group by CustomerId, IsBuy

order by VisitDate;

Re: How to get the first Num

Fri, 12 Jul 2013 15:26:57 GMT

try this one:

If(previous(CustomerId)=Custemerid and IsBuy=1,'Yes','No') as FirstRecord

you have order it by Customerid and Date,

Let me know if works

cheers

Re: How to get the first Num

swuehl — Fri, 12 Jul 2013 15:52:08 GMT

If I use this, I do get a different outcome than expected:

CustomerId	VisitDate	IsBuy	FirstRecord
1	2013-1-1	0	No
1	2013-2-1	0	No
1	2013-3-1	1	Yes
1	2013-4-1	1	Yes
2	2013-1-1	1	No
3	2013-5-1	0	No
3	2013-6-1	1	Yes

Re: How to get the first Num

Fri, 12 Jul 2013 16:13:49 GMT

Hello swuhl , you are correct,

could it be possible a if stament instead of a left join... or is just the only way to do it

cheers,

Re: How to get the first Num

swuehl — Fri, 12 Jul 2013 16:18:59 GMT

Potentially yes, but I think not in one load run. The issue is, you need to flag the first buy, and only the first.

edit: You actually can. But I don't think using peek() or previous() function, instead using autonumber():

INPUT:

LOAD * INLINE [

CustomerId, VisitDate, IsBuy

1, 2013-1-1, 0

1, 2013-2-1, 0

1, 2013-3-1, 1

1, 2013-4-1, 1

2, 2013-1-1, 1

3, 2013-5-1, 0

3, 2013-6-1, 1

];

LOAD *,

if( autonumber(if(IsBuy, VisitDate),CustomerId) = 1,'Yes','No') as FirstBuy

RESIDENT INPUT

ORDER BUY CustomerId, VisitDate;

Re: How to get the first Num

Wed, 17 Jul 2013 19:29:41 GMT

Thanks Swuehl