Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

Break out timestamps to another column

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. 

2 Replies
Creator II
Creator II


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. 

Creator II
Creator II

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

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.