Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

aboumejjane
Contributor

order by issue when using intervalmatch

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

1 Reply
MVP & Luminary
MVP & Luminary

Re: order by issue when using intervalmatch

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;


talk is cheap, supply exceeds demand