Limit load to X records per group

[cicciput]

I have a huge login table, with all the login times of all users since ages.

How do I limit load to last, say, 10 logins per user so I can have a glimpse on their habits without the burden?

Something like:

LOAD Only(UserID),

     FirstValues(LoginDate, 10)

GROUP BY UserID

ORDER BY LoginDate DESC;

I tried searching for an answer with no luck.

Thanks for your help

[tomasz_tru]

Maybe try: FirstSortedValue - script function ‒ QlikView

[cicciput]

Hello Tomasz,

well, FirstSortedValue, as far as I ever used it, returns one result, one row.

I need maybe 10 results, 10 rows

[tomasz_tru]

But you can loop though rank parameter ten times. Ugly, but may work...

[antoniotiman]

Hi Renzo,maybe this

Temp:

LOAD * From Table;

LOAD UserID,LoginDate

Resident Temp Where AutoNumber(RowNo(),UserID) <=10

Order By UserID,LogineDate Desc;

Drop Table Temp;

Regards,

Antonio

[cicciput]

Ciao Antonio,

I'm sorry, this returns an empty set 😞

Tmp:

LOAD * INLINE [

    UserID, LoginDate

    1, 09/6/2017

    1, 10/6/2017

    1, 14/6/2017

    1, 17/6/2017

    2, 17/6/2017

    2, 19/6/2017

    2, 24/6/2017

    2, 27/6/2017

];

Login:

LOAD UserID,

     LoginDate

RESIDENT Tmp

WHERE AutoNumber(RowNo(), UserID) <= 2

ORDER BY UserID, LoginDate Desc;

DROP TABLE Tmp;

Any advice?

[Frank_Hartmann]

LOAD * INLINE [

    UserID, LoginDate

    1, 09/6/2017

    1, 10/6/2017

    1, 14/6/2017

    1, 17/6/2017

    2, 17/6/2017

    2, 19/6/2017

    2, 24/6/2017

    2, 27/6/2017

];

Login:

LOAD UserID,

     LoginDate,  

     RowNo()

RESIDENT Tmp

WHERE AutoNumber(RowNo(), UserID) <= 2

ORDER BY UserID, LoginDate Asc; DROP Table Tmp;

[tomasz_tru]

Use alias on any field in Login table, otherwise the data will be added to temp table, and then dropped.

[antoniotiman]

Tmp:
LOAD *,AutoNumber(RowNo(), UserID) as A INLINE [
UserID, LoginDate
1, 09/6/2017
1, 10/6/2017
1, 14/6/2017
1, 17/6/2017
2, 17/6/2017
2, 19/6/2017
2, 24/6/2017
2, 27/6/2017
];
Login:
LOAD UserID,
LoginDate
RESIDENT Tmp
WHERE A <= 2
ORDER BY UserID, LoginDate Desc;
DROP TABLE Tmp;

[antoniotiman · Accepted solution]

Or Add Noconcatenate

Tmp:
LOAD * INLINE [
UserID, LoginDate
1, 09/6/2017
1, 10/6/2017
1, 14/6/2017
1, 17/6/2017
2, 17/6/2017
2, 19/6/2017
2, 24/6/2017
2, 27/6/2017
];
Login:
Noconcatenate
LOAD UserID,
LoginDate
RESIDENT Tmp
WHERE AutoNumber(RowNo(), UserID) <= 2
ORDER BY UserID, LoginDate Desc;
DROP TABLE Tmp;