Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Converting SQL script to Qlikview

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

1 Solution

Accepted Solutions

Re: Converting SQL script to Qlikview

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

4 Replies

Re: Converting SQL script to Qlikview

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

Not applicable

Re: Converting SQL script to Qlikview

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

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

Not applicable

Re: Converting SQL script to Qlikview

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

Community Browser