Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Try using Peek/Previous
LOAD *,
If(UserID = Previous(UserID), RangeSum(Peek('Sequence'), 1), 1) as Sequence
Resident Fact
Order By UserID, Date;
Can you try Autonumber(UserID, Date) as sequence
This does not deliver correct results
Try using Peek/Previous
LOAD *,
If(UserID = Previous(UserID), RangeSum(Peek('Sequence'), 1), 1) as Sequence
Resident Fact
Order By UserID, Date;
Try below
Autonumber(UserID&Date, UserID) as instanceID