topic Re: Converting SQL script to Qlikview in QlikView

Converting SQL script to Qlikview

Thu, 01 Dec 2011 14:26:20 GMT

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

Re: Converting SQL script to Qlikview

rwunderlich — Thu, 01 Dec 2011 19:05:31 GMT

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

Re: Converting SQL script to Qlikview

Thu, 01 Dec 2011 19:15:54 GMT

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.

Re: Converting SQL script to Qlikview

rwunderlich — Thu, 01 Dec 2011 23:15:52 GMT

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

Re: Converting SQL script to Qlikview

Wed, 18 Jan 2012 20:18:09 GMT

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