Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table that looks something like this:
Which is a log of users performing different categories of activites. Due to the nature of the system that produces this log, I do not have an end timestamp for these activities, it only records when an activity is started. Does anyone have any ideas how I can break out the starting timestamp of the next activity to another column, so that the table looks something like this:
The data is from an SQL database that I load into Qlik by query, so if there are any SQL wizards here with a neat trick to get this done right at the source, I'm all ears. Or if there's some Qlik trick that can append such a column to the table afterwards, that's just as fine by me. I really have no clue where to even start with this.
Hi.
You can load data by query you have already, then sort result by user ASC, date desc, starttime desc and use previous(startTime) function to determine endTime of current action.
Sample:
load * inline [
date, user, activity, startTime
2021-05-23, 1, 1, 12:00
2021-05-23, 1, 2, 14:00
2021-05-23, 1, 3, 17:00
2021-05-23, 2, 1, 12:00
2021-05-23, 2, 2, 18:00
];
NoConcatenate
Result:
load *
, if(user=Previous(user)
, Previous(startTime)
) as endTime
Resident Sample
order by user asc, date desc, startTime desc;
drop table Sample;
This code will give a table looking like this:
Hope it helps.