Number ordered sequence of group during load

[nigel987]

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]

Can you try Autonumber(UserID, Date) as sequence

[nigel987]

This does not deliver correct results

[sunny_talwar · Accepted solution]

Try using Peek/Previous

LOAD *,

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

Resident Fact

Order By UserID, Date;

[vinieme12]

Try below

Autonumber(UserID&Date, UserID)  as instanceID