Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

jennlal1
New Contributor

Issue with PEEK and Rank duplicating rows

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

4 Replies
eduardo_dimperi
Valued Contributor II

Re: Issue with PEEK and Rank duplicating rows

Could you please add the expect output to compare?

jennlal1
New Contributor

Re: Issue with PEEK and Rank duplicating rows

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
Valued Contributor III

Re: Issue with PEEK and Rank duplicating rows

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 Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
thiago_justen
Valued Contributor III

Re: Issue with PEEK and Rank duplicating rows

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

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
Community Browser