Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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
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.
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.
Is fantastic, i needed.
Than you very much.
Regards.
Ivan.