topic Re: Group in QlikView

Group

Mon, 30 Jan 2012 14:55:53 GMT

Good day, can you help me?.

I have the following table:

user_id	bug_id	date_modified
33	3615	1201550061
33	3615	1201550061
33	3615	1201550061
33	3615	1201550061
33	3615	1201550061
33	3615	1201550061
33	3615	1201550061
33	3615	1201550239
33	3615	1201550259
33	3615	1201550259
2	3615	1201552220
2	3615	1201552228
34	3615	1204037843
34	3615	1204037846
2	3615	1204048000
2	3615	1204048000
2	3615	1204048033
2	3615	1204048033
2	3615	1204138273
2	3615	1208202609
2	3615	1208202609

I must subtract the date greater with the minor by user_id, but respecting the order.

I need the following:

user_id	bug_id	date_modified
33	3615	198
2	3615	8
34	3615	3
2	3615	4154609

Regards!.

Ivan.

Re: Group

swuehl — Mon, 30 Jan 2012 15:09:36 GMT

Maybe like attached?

I am not sure what you are doing with bug_id, but since you said "by user_id", it seems to me that you don't need to group by or check bug_id.

I used this load statement:

LOAD recno() as RecID,

user_id,

bug_id,

date_modified,

if(RecNo()=1,1,if(peek(user_id)=user_id,peek( user_group_id), peek(user_group_id)+1)) as user_group_id

FROM

[http://community.qlik.com/thread/46220?tstart=0]

(html, codepage is 1252, embedded labels, table is @1);

creating a user_group_id with e.g. two distinct ids for user_id 2.

Then I created a chart with dimensions user_group_id, user_id, bug_id (and I've hided the user_group_id in presentation tab), and as expression:

=max(date_modified)-min(date_modified)

Hope this helps,

Stefan

Group

Mon, 30 Jan 2012 15:53:16 GMT

Great!!..

bug_id is also a field to grouping.

user_id	bug_id	date_modified
33	3615	1201550061
33	3615	1201550061
33	3615	1201550061
33	3615	1201550061
33	3615	1201550061
33	3615	1201550061
33	3615	1201550061
33	3615	1201550239
33	3615	1201550259
33	3615	1201550259
2	3615	1201552220
2	3615	1201552228
34	3615	1204037843
34	3615	1204037846
2	3615	1204048000
2	3620	1204048000
5	3620	1204048033
5	3620	1204048033
5	3620	1204138273
2	3620	1208202609
2	3620	1208202609

How he added this field in the band?.

Thank you very much!!

Ivan.

Re: Group

swuehl — Mon, 30 Jan 2012 15:58:39 GMT

Just extend the if() clause:

LOAD recno() as RecID,

user_id,

bug_id,

date_modified,

if(RecNo()=1,1,

if(peek(user_id)=user_id and peek(bug_id)=bug_id,peek( user_group_id), peek(user_group_id)+1)) as user_group_id

FROM

[http://community.qlik.com/thread/46220?tstart=0]

(html, codepage is 1252, embedded labels, table is @3);

edit:

You need to uncheck Suppress Zero Values in presentation tab to see all user_group_id values, also the ones with zero difference in date_modified.

Group

Mon, 30 Jan 2012 16:55:29 GMT

Is fantastic, i needed.

Than you very much.

Regards.

Ivan.