topic Number ordered sequence of group during load in QlikView

Number ordered sequence of group during load

nigel987 — Fri, 07 Apr 2017 10:25:52 GMT

Hi,

I have the following table.

UserID, Date, Item

1, 01.01.2017, 5 -> 1

1, 01.03.2017, 5 -> 3

1, 01.02.2017, 5 -> 2

2, 01.02.2017, 5 -> 1

2, 01.03.2017, 5 -> 2

I want to create a row number for each group (UserID), but within the group the rows need to be sorted by Date.

I need to do this in the load script, as I have more than 5Mio UserIDs.

Can someone give me a hint? I tried the following, and it worked for a small test set, but for the real dataset calculation crashes after hours:

Load *,

AutoNumber(RecNo(),UserID) AS Sequence

Resident Fact

Order By UserID,Date;

Thanks!

udaya_kumar — Fri, 07 Apr 2017 10:43:40 GMT

Can you try Autonumber(UserID, Date) as sequence

nigel987 — Fri, 07 Apr 2017 11:00:47 GMT

This does not deliver correct results

sunny_talwar — Fri, 07 Apr 2017 11:15:44 GMT

Try using Peek/Previous

LOAD *,

If(UserID = Previous(UserID), RangeSum(Peek('Sequence'), 1), 1) as Sequence

Resident Fact

Order By UserID, Date;

vinieme12 — Fri, 07 Apr 2017 12:46:54 GMT

Try below

Autonumber(UserID&Date, UserID) as instanceID