Issue with PEEK and Rank duplicating rows

[Anonymous]

Good afternoon,

I have been working on ranking inside my script and I am having issue with certain rows duplicating.  There is no rhyme or reason for it that I can see in my raw data.  There are multiple functions I have used and end up with the same results.   We are ranking on the MRN level.  Am I missing a where clause in this?

LEFT JOIN (Main)
LOAD I_MRI_PATN, D_ADMS_PATN, RANKVISIT
  ;
LOAD  I_MRI_PATN, D_ADMS_PATN,
IF(PEEK(I_MRI_PATN)<>I_MRI_PATN,1,PEEK(RANKVISIT)+1) AS RANKVISIT
Resident Main
Order by I_MRI_PATN, D_ADMS_PATN

or

LEFT JOIN (Main)
LOAD I_ACCN_PATN, RANKVISIT, DAYS_BETWEEN
  ;
LOAD  I_MRI_PATN,I_ACCN_PATN, D_ADMS_PATN,
IF(I_MRI_PATN<>Previous (I_MRI_PATN),1,PEEK(RANKVISIT)+1) AS RANKVISIT,
Resident Main
Order by I_MRI_PATN,D_ADMS_PATN ;

Below is an example of the output I am receiving:

FIN          I_MRN_PATN     RANKVISIT

12345        12                           1

12346        12                           2

12347        12                           3

12348        12                           4

12349        12                           5

12349        12                           6

12350        12                           5

12350        12                           6

[eduardo_dimperio]

Could you please add the expect output to compare?

[Anonymous]

The RANKVISIT should go from 1 to 6, but for some reason my data is being duplicated on the FIN level for only some accounts.  I highlighted what my issue is below.

Current State where the FINs are the same but  have duplicated on the ranking column.

FIN          I_MRN_PATN     RANKVISIT

12345        12                           1

12346        12                           2

12347        12                           3

12348        12                           4

12349        12                           5

12349        12                          6

12350        12                           5

12350        12                          6

The data should come out as below: 

FIN          I_MRN_PATN     RANKVISIT

12345        12                           1

12346        12                           2

12347        12                           3

12348        12                           4

12349        12                           5

12350        12                           6

[Thiago_Justen_]

Jennifer,

Why don't you use rank function? If you really need do this ranking into the script, maybe this will help you.

Rank by Name &amp; amount in script

[Thiago_Justen_]

If the link above doesn't solve your problem try using the Group By clause.