topic Re: order by issue when using intervalmatch in QlikView

order by issue when using intervalmatch

Anonymous — Sun, 03 Nov 2013 10:33:03 GMT

Hi Guys,

I'm having a problem with the 'order by' when I use the interval match function.

I've created a script that peeks to the previous date of an employee that called called sick.

However after using the interval match function the Order by seem not to be working right.

The other problem is that when I peek to the previous date of an employee, it doesn't work 100%.

For example if I look at the first record it can't peek to the previous loaded record, since there is no previous date available before the first date some one called sick, so null should be returned.

Please look at my script.

Hope some one can help!

Cheers!

Sam

Re: order by issue when using intervalmatch

Gysbert_Wassenaar — Sun, 03 Nov 2013 18:08:55 GMT

IntervalMatch: // <-- meaningless since the result is joined to Tmp
left join(Tmp)
IntervalMatch (datum.KEY)
Load distinct startdate, endDate resident Tmp
Order by startdate; // <-- doesn't do anything because of the join

When you join it with Tmp the order by clause is meaningless. If you want to reorder Tmp to calculate PreviousStartdate and PreviousNumberofDays then you need to do that afterwards:

x:
NoConcatenate LOAD
*,
Peek(startdate,-1) as PreviousStartdate,
peek(numberofdays,-1) as PreviousNumberofDays
Resident Tmp
order by startdate;

drop table Tmp;