topic Re: comparing value and taking minimum in QlikView

comparing value and taking minimum

Thu, 22 Mar 2012 09:56:36 GMT

hi everybody,

I have following issue to solve... I just want the rows with minimum time.

I have data looks like the following table:

Table1:

OrderId	Time
AA	00:15
BB	01:10
BB	01:05
BB	01:00
CC	00:32
CC	00:30
DD	00:23
EE	00:45
EE	00:08

I need:

Table2:

OrderId	Time
AA	00:15
BB	01:00
CC	00:30
DD	00:23
EE	00:08

I have starting write a script like this:

LOAD

OrderId,

Time,

if(peek('OrderId')=OrderId, if(peek('Time)<Time, peek(Time),Time),Time) as Testing

FROM Table1;

My result is like Table1 nothin is change.

Thx in advance!!!

Re: comparing value and taking minimum

perumal_41 — Thu, 22 Mar 2012 10:04:22 GMT

Use this Script get ur Result

LOAD

OrderId,

Time

FROM Table1;

Left join

LOAD

OrderId,

min(Time) as Testing

FROM Table1 Group by OrderId ;

Regards

Perumal A

Re: comparing value and taking minimum

Thu, 22 Mar 2012 10:12:50 GMT

Hia,

Try this:

table1:

LOAD

OrderId,

Time

from table1;

NoConcatenate

table2:

load

OrderId,

min(0+Time) as Time

Resident table1

Group By OrderId;

drop Table table1;

Re: comparing value and taking minimum

Thu, 22 Mar 2012 11:54:36 GMT

Thx for quick response!

But why I still don't get my minimum value from column 'TIME'.

My basis table look like this:

OrderID	OrderDate	SessionDate	OrderDate-SessionDate= Diff Time
AA	2011-07-15 12:07:00	2011-07-15 12:00:00	00:07
BB	2011-11-06 21:12:00	2011-11-06 21:00:00	00:12
BB	2011-11-06 21:12:00	2011-11-06 21:06:00	00:06
BB	2011-11-06 21:12:00	2011-11-06 21:20:00	-00:08
CC	2011-03-18 23:04:00	2011-03-18 23:00:00	00:04
CC	2011-03-18 23:04:00	2011-03-18 23:10:00	-00:06
DD	2011-02-08 14:08:00	2011-02-08 14:07:00	00:01

As result I want:

OrderId	Diff Time
AA	00:07
BB	00:06
CC	00:04
DD	00:01

How I can handle the negative time value in column 'Diff Time' ????

Maybe that is my problem.

Re: comparing value and taking minimum

perumal_41 — Thu, 22 Mar 2012 12:02:28 GMT

LOAD

OrderId,

min(fabs(TimeField )) as Testing

FROM Table1 Group by OrderId ;

Try this Scipt

Regards

Perumal A

Re: comparing value and taking minimum

Thu, 22 Mar 2012 13:05:35 GMT

THX IT WORKS!!!!