topic Re: create min&max timestamp in QlikView

create min&max timestamp

stabben23 — Wed, 30 Aug 2017 06:18:33 GMT

Hi,

I have problem to create min and max timestamp. My rawdata looks like this.

expected below, I can not use a min and max and do a Group by on Dimension because all dimension will be repeated.

This has to be done in the load script.

dimension	min	max
44-150	2017-08-25 11:00:00	2017-08-25 11:01:59
50-100	2017-08-25 11:02:59	2017-08-25 11:05:59
63-125	2017-08-25 11:06:59	2017-08-25 11:08:59
50-100	2017-08-25 11:09:59	2017-08-25 11:10:59
63-125	2017-08-25 11:11:59	2017-08-25 11:13:59

Re: create min&max timestamp

vinieme12 — Wed, 30 Aug 2017 06:26:10 GMT

LOAD Dimension,

Min(Time) as MinTime,

Max(Time) as MaxTime

From XXXX

Group by Dimension;

Re: create min&max timestamp

stabben23 — Wed, 30 Aug 2017 06:33:54 GMT

Hi Vineeth,

as I said, this will not work, for ex this will give me one row on 50-100 With MinTime 2017-08-25 11:02:59 and MaxTime 2017-08-25 11:10:59 that is not correct.

Re: create min&max timestamp

antoniotiman — Wed, 30 Aug 2017 07:11:00 GMT

Hi Staffan,

try like this

LOAD *,If(Peek(Dimension)=Dimension,Peek(Counter),RangeSum(Peek(Counter),1)) as Counter

From Table;

Than LOAD Min and Max Grouped By Counter.

Regards,

Antonio

Re: create min&max timestamp

stabben23 — Wed, 30 Aug 2017 07:21:32 GMT

Hi Antonio,

YES, this will create a Group of all Dimension and add a Field that can be used as a keyfield, thanks.

I will test this in my script and comes back to you.

Re: create min&max timestamp

its_anandrjs — Wed, 30 Aug 2017 07:25:03 GMT

Try this way also

LOAD

dimension,

TimeStamp( Min(Num(Time)) ) as Min_Time,

TimeStamp( Max(Num(Time)) ) as Max_Time

From Source

Group by dimension;

Re: create min&max timestamp

stabben23 — Wed, 30 Aug 2017 07:32:02 GMT

Hi Anand,

thanks but this is not working as I said. to do a Group by only on Dimension will gives me 3 rows instead of 5.

antoniotiman‌ has solve this for me.