Converting SQL script to Qlikview

[#1]

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.

For example,

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.

Thanks,

Keith

[rwunderlich]

If DisDay never repeats for a SSN, you can user autonumber like this.

data2:

LOAD *,

          AutoNumber(Disday, SSN) as Rank

RESIDENT data

ORDER BY SSN, Disday desc

;

Example attached.

If DisDay repeats, there is a different way I can suggest.

-Rob

http://robwunderlich.com

[#3]

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.

Thanks again.

[rwunderlich · Accepted solution]

Ok, so try this:

data2:

LOAD *,

          if(SSN = Previous(SSN), peek('Rank',-1)+1, 1) as Rank

RESIDENT data

ORDER BY SSN, Disday desc

;

I didn't add in the admit date -- you can do that. Example attached.

-Rob

[#5]

Sorry about the long delay.  I haven't actually had to work off the QVD file until now.  The script worked on first try.