topic Re: Top 1 Entry; Order By Errors in QlikView

Top 1 Entry; Order By Errors

Tue, 24 Jun 2014 17:30:22 GMT

[Loss Mit Steps 999]:

LOAD [Loan Number],
[ Setup Date],
[ Step Code],
[Completion Date] as s367_Dt,
[Record Change Code]
FROM
(qvd)
where match([ Step Code],'367')

and match([Record Change Code],'x','c')

Order By [Completion Date] desc ;

I then want to choose the first instance only or the most recent by date.

I researched where you can use First (1) [Completion Date]

However when I run the above named code I keep getting garbage after error when I include the Order By statement. When I exclude the Order By Statement, no problems. Ultimatley I wish to get the top 1 record based on the Completion Date. Any ideas here??

Re: Top 1 Entry; Order By Errors

Gysbert_Wassenaar — Tue, 24 Jun 2014 17:55:23 GMT

You can only use Order By with a resident load. You're loading data from a qvd so you can't use Order By in that load statement.

[Loss Mit Steps 999]:
LOAD [Loan Number], 
[ Setup Date], 
[ Step Code], 
[Completion Date] as s367_Dt,
[Record Change Code]
FROM
(qvd)
where match([ Step Code],'367')
and match([Record Change Code],'x','c')

Result:
First 1
noconcatenate Load * 
resident [Loss Mit Steps 999]
Order by  s367_Dt desc ;

drop table [Loss Mit Steps 999];

Re: Top 1 Entry; Order By Errors

Tue, 24 Jun 2014 17:58:16 GMT

Hi:

Try this:

[Loss Mit Steps 999]:

LOAD

FirstValue([Loan Number]) as [Loan Number],
FirstValue([ Setup Date]) as [ Setup Date],
FirstValue([ Step Code]) as [ Step Code],
Max([Completion Date]) as s367_Dt,
FirstValue([Record Change Code]) as [Record Change Code]
FROM
(qvd)
where match([ Step Code],'367')

and match([Record Change Code],'x','c')

Order By [Completion Date] desc ;

Best regards.

Re: Top 1 Entry; Order By Errors

Tue, 24 Jun 2014 18:12:14 GMT

Hi Jackson, In Qlikview, we can sort the table data only on Resident Loads only. Please follow below procedure.

TEMP:

LOAD *

FROM SOURCETABLE.qvd (qvd) ;

MAIN:

Noconcatenate

LOAD * Resident TEMP Order BY FIELDNAME