topic Re: Extract Min and Max Dates in QlikView

Extract Min and Max Dates

balanandam — Fri, 04 May 2018 10:29:09 GMT

HI All,

I have some data like below:

loginTime	logoutTime	SessionID	username	Logindate
01-05-2018 05:07:18	01-05-2018 05:37:41	155	testing123	01-05-2018
01-05-2018 05:37:41	01-05-2018 05:37:46	156	testing123	01-05-2018
01-05-2018 05:37:46	01-05-2018 05:39:35	157	testing123	01-05-2018
01-05-2018 05:39:35	01-05-2018 07:17:26	158	testing123	01-05-2018
01-05-2018 07:17:26	01-05-2018 07:21:52	159	testing123	01-05-2018
01-05-2018 07:21:52	01-05-2018 07:22:11	160	testing123	01-05-2018
01-05-2018 07:22:11	01-05-2018 07:23:00	161	testing123	01-05-2018
01-05-2018 07:23:50	01-05-2018 07:25:43	162	testing123	01-05-2018

I wanted min & max dates for each day by user. Can you help me out here ?

Attaching App & sample data in Excel

Thanks In advance!

Br, Balanandam

Re: Extract Min and Max Dates

rubenmarin — Fri, 04 May 2018 10:49:50 GMT

Hi Balanandam, time is loaded as a string, you need to convert to time using timestam#(), ie.:

=Timestamp(min(Timestamp#(loginTime, 'DD-MM-YYYY hh:mm:ss')))

Re: Extract Min and Max Dates

balanandam — Fri, 04 May 2018 10:59:51 GMT

Thanks Ruben for the response!

I need data like, one row for every unique combination of Date,username. I believe I need to apply aggr() in expression.

Minmum date group by Date & username combination

Data I need as below:

testing123	19-04-2018	19-04-2018 11:53:01
testing123	20-04-2018	20-04-2018 00:47:29

Br, Balanandam

Re: Extract Min and Max Dates

rubenmarin — Fri, 04 May 2018 11:15:07 GMT

Not really, if you alredy have username and date as dimension there is no need to use aggr, the default behaviour is to calculate the measure for each combination of the dimensions.

Re: Extract Min and Max Dates

balanandam — Fri, 04 May 2018 11:22:48 GMT

Hi Ruben,

some how it is not working...

if you see app attached by you the Date & username combination is repeating...

Br, Balanandam

Re: Extract Min and Max Dates

rubenmarin — Fri, 04 May 2018 11:27:43 GMT

That would be because these are different dates, althought you only see the date internally it's storing the time.

You can load using floor to ignore the time and have only one date, or use a calculated dimension that removes the time.

Re: Extract Min and Max Dates

balanandam — Fri, 04 May 2018 11:54:32 GMT

Awesome!!