I have two tables:
Documents:
and Events:
| DOC_ID | EVENT | TIME |
|---|
| 1 | CREATED | 2018-05-14 10:00:00 |
| 1 | PUBLISHED | 2018-05-15 13:04:00 |
| 2 | CREATED | 2018-02-03 11:45:12 |
| 2 | PUBLISHED | 2018-03-05 10:26:15 |
What I would like to achieve is table which looks like this:
| ID | NAME | CREATED | PUBLISHED | DURATION |
|---|
| 1 | Doc1 | 2018-05-14 10:00:00 | 2018-05-15 13:04:00 | 1d 3h 4m |
| 2 | Doc2 | 2018-02-03 11:45:12 | 2018-03-05 10:26:15 | 39d 22h 41m 3s |
I have no idea how could I achieve it with features available with QlikSense. I was trying to add two columns - created:
=IF(EVENT='CREATED', time)
and published:
=IF(EVENT='PUBLISHED', time)
but then I get results which more less looks like that:
| ID | NAME | CREATED | PUBLISHED | DURATION |
|---|
| 1 | Doc1 | 2018-05-14 10:00:00 | | |
| 1 | Doc1 | | 2018-03-05 10:26:15 | |
| 2 | Doc2 | 2018-02-03 11:45:12 | | |
| 2 | Doc2 | | 2018-03-05 10:26:15 | |
I would appreciate any hints and suggestions.
