Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
[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??
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];
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.
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