Discussion Board for collaboration on QlikView Scripting.
Can someone tell me how to convert the following T-SQL field into Qlikview Script?
Row_Number() over (partition by SSN order by DisDay desc)
This basically will assign a rank by SSN to assign a descending list of patient visits where the list will start over for each SSN.
SSN Disday Rank
10 12/11 1
10 11/11 2
10 10/11 3
20 11/11 1
20 10/11 2
30 11/11 1
I've tried using the iterno() function but can't seem to completely grasp it.
Go to Solution.
Ok, so try this:
if(SSN = Previous(SSN), peek('Rank',-1)+1, 1) as Rank
ORDER BY SSN, Disday desc
I didn't add in the admit date -- you can do that. Example attached.
If DisDay never repeats for a SSN, you can user autonumber like this.
AutoNumber(Disday, SSN) as Rank
If DisDay repeats, there is a different way I can suggest.
One more try now that I got someone helping...
SSNs may have more than one discharge per day.
The full criteria would be discharge date desc, admit date desc
If those overlap the order is not important.
Basically I am looking at readmission data for a hospital network across the US.
Thanks for your help. I come from a T-SQL & Oracle background so your help is very appreciated.
Sorry about the long delay. I haven't actually had to work off the QVD file until now. The script worked on first try.