Help with lowest and highest value

[qw_johan]

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

[#2]

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;

br

Martin

[qw_johan]

Hi,

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

Thanks

[#4]

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..

[qw_johan]

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.

[#6]

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

[swuehl · Accepted solution]

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

[qw_johan]

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