Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Top 1 Entry; Order By Errors

 

[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??

3 Replies

Re: Top 1 Entry; Order By Errors

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];


talk is cheap, supply exceeds demand
Not applicable

Re: Top 1 Entry; Order By Errors

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.

Not applicable

Re: Top 1 Entry; Order By Errors

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

Community Browser