Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

comparing value and taking minimum

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:

OrderIdTime
AA00:15
BB01:10
BB01:05
BB01:00
CC00:32
CC00:30
DD00:23
EE00:45
EE00:08

I need:


Table2:

OrderIdTime
AA00:15
BB01:00
CC00:30
DD00:23
EE00: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!!!

1 Solution

Accepted Solutions
perumal_41
Valued Contributor II

Re: comparing value and taking minimum

Hi

LOAD

OrderId,

min(fabs(TimeField )) as Testing

FROM Table1 Group by OrderId ;

Try this Scipt

Regards

Perumal A

5 Replies
perumal_41
Valued Contributor II

Re: comparing value and taking minimum

Hi

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

Not applicable

Re: comparing value and taking minimum

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;

Not applicable

Re: comparing value and taking minimum

Thx for quick response!

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

My basis table look like this:

OrderIDOrderDateSessionDateOrderDate-SessionDate= Diff Time
AA2011-07-15 12:07:002011-07-15 12:00:0000:07
BB2011-11-06 21:12:002011-11-06 21:00:0000:12
BB2011-11-06 21:12:002011-11-06 21:06:0000:06
BB2011-11-06 21:12:002011-11-06 21:20:00-00:08
CC2011-03-18 23:04:002011-03-18 23:00:0000:04
CC2011-03-18 23:04:002011-03-18 23:10:00-00:06
DD2011-02-08 14:08:002011-02-08 14:07:0000:01

As result I want:

OrderIdDiff Time
AA00:07
BB00:06
CC00:04
DD00:01

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

Maybe that is my problem.

perumal_41
Valued Contributor II

Re: comparing value and taking minimum

Hi

LOAD

OrderId,

min(fabs(TimeField )) as Testing

FROM Table1 Group by OrderId ;

Try this Scipt

Regards

Perumal A

Not applicable

Re: comparing value and taking minimum

THX IT WORKS!!!!

Community Browser