How to get the first Num

[#1]

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.

[swuehl]

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;

[#3 · Accepted solution]

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

[swuehl]

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

[#5]

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,

[swuehl]

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;

[#7]

Thanks Swuehl