topic Help with lowest and highest value in QlikView

Help with lowest and highest value

qw_johan — Wed, 02 Nov 2011 21:41:51 GMT

Hi,

I have a long list of id numbers, dates and times(start and end time).

My problem is that certain dates have more that one start and end time (see picture below).

What I'd like to get is a list with only the lowerst start time and the highest end

time on the dates where there are several start/end times..the rest should remain like they are.

For examples with the marked dates in the picture below...

20110125 0725 1530

20110201 0710 1535

20110203 0700 1630

20110207 0705 1730

Can this be accomplished in qlikview?

Thanks

Help with lowest and highest value

Wed, 02 Nov 2011 21:55:04 GMT

Hello,

Try to use min and max to solve this.

E.g.

Load

Date,

min(Start) as Start,

max(End) as End

From xxx

group by Date;

Martin

Help with lowest and highest value

qw_johan — Thu, 03 Nov 2011 06:15:00 GMT

Hi,

I have tried that but I get a script error saying "Invalid expression".

Thanks

Re: Help with lowest and highest value

Thu, 03 Nov 2011 09:06:28 GMT

Hi John,

Try this

DateTime:

load * Inline

[Date,Start,End

20110125, 0725, 1530

20110125, 0720, 1520

20110201, 0710, 1535

20110201, 0715, 1525

20110203, 0750, 1650

20110203, 0700, 1630

20110207, 0708, 1740

20110207, 0705, 1730

];

Load

Date as Date1,

Min(Start) as MinStart,

Max(End) as MaxEnd

Resident DateTime

group by Date;

Reply me if it works for you or not!!

Hope it will helps you

Regards..

Re: Help with lowest and highest value

qw_johan — Thu, 03 Nov 2011 20:11:13 GMT

No, it doesn't work. Doesn't make a difference.

This is my code...

WorkTimes:

LOAD id,

date_f,

min(time_f) as time_f,

date_t,

max(time_t) as time_t,

break_min

FROM

TB03_20111103_205945.xls(biff, embedded labels, table is Sheet1$)

group by id, date_f, time_f, date_t, time_t, break_min;

I upload a qvw and excel file for example.

Re: Help with lowest and highest value

Thu, 03 Nov 2011 20:37:19 GMT

try using the attached logic in your script ..think it should give you the results you wish..

Help with lowest and highest value

swuehl — Thu, 03 Nov 2011 22:34:05 GMT

Seems like you don't know how to handle the two dates (start date and end date).

You should group by only id, one date and no time. I assumed that sum(break_min) is something meaningful, but don't know.

Try maybe like this:

LOAD id,

date_f,

min(time_f) as time_f,

max(date_t) as date_t,

max(time_t) as time_t,

sum(break_min) as break_min

FROM

TB03_20111103_205945.xls(biff, embedded labels, table is Sheet1$)

group by id, date_f;

Regards,

Stefan

Re: Help with lowest and highest value

qw_johan — Fri, 04 Nov 2011 14:59:38 GMT

Thanks Stefan. This was exactly what I was looking for.