topic Re: Number rows grouped by user_id in QlikView

Number rows grouped by user_id

Mon, 18 May 2015 13:22:59 GMT

Hi,

I´m facing a Problem:

Let´s say I have a Table like:

id	user_id	date
1	1	2015-01-01
2	2	2015-01-01
3	1	2015-01-02
4	1	2015-01-03
5	2	2015-01-03
6	3	2015-01-01

What i want to achieve:

I want to number the rows grouped by the field "user_id". the row with the lowest date should be "1", the row with the second lowest date should be "2", and so on.

Result should be like:

id	user_id	date	Number
1	1	2015-01-01	1
2	2	2015-01-01	1
3	1	2015-01-02	2
4	1	2015-01-03	3
5	2	2015-01-03	2
6	3	2015-01-01	1

Can somebody please help me. I couldn´t find any solution.

Thank you

Re: Number rows grouped by user_id

Gysbert_Wassenaar — Mon, 18 May 2015 13:38:00 GMT

Like this:

MyTable:

LOAD

id,

user_id,

date,

autonumber(RecNo(),user_id) as Number

FROM ...mysource...;

Re: Number rows grouped by user_id

pokassov — Mon, 18 May 2015 13:40:49 GMT

Hello!

Can you change your script to achieve your goal?

If the answer is 'Yes' then:

t2:

noconcatenate

load id, user_id, date,

if(previous(user_id)<>user_id,1,peek('number_user')+1) as number_user

resident your_table

order by user_id, date

left join (your_table)

load id, nu,ber_user

resident t2;

drop table t2;

Re: Number rows grouped by user_id

Mon, 18 May 2015 13:57:35 GMT

wow, thank you!