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.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
bonvin
Contributor II
Contributor II

Break out timestamps to another column

I have a table that looks something like this: 

bonvin_0-1621718821246.png

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: 

bonvin_1-1621719195343.png

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
mfchmielowski
Creator II
Creator II

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. 

mfchmielowski
Creator II
Creator II

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:

mfchmielowski_0-1621786423945.png

Hope it helps.