create min&max timestamp

[stabben23]

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

[vinieme12]

LOAD Dimension,

     Min(Time) as MinTime,

     Max(Time) as MaxTime

From XXXX

Group by Dimension;

[stabben23]

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.

[antoniotiman · Accepted solution]

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

[stabben23]

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.

[its_anandrjs]

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;

[stabben23]

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.