4 Replies Latest reply: Jan 13, 2018 11:50 PM by Thiago Justen Teixeira RSS

    Issue with PEEK and Rank duplicating rows

    Jennifer Lal

      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

        • Re: Issue with PEEK and Rank duplicating rows
          Eduardo DImperio

          Could you please add the expect output to compare?

            • Re: Issue with PEEK and Rank duplicating rows
              Jennifer Lal

              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

               

            • Re: Issue with PEEK and Rank duplicating rows
              Thiago Justen Teixeira

              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 & amount in script

              • Re: Issue with PEEK and Rank duplicating rows
                Thiago Justen Teixeira

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