topic Issue with PEEK and Rank duplicating rows in QlikView

Issue with PEEK and Rank duplicating rows

Anonymous — Thu, 04 Jan 2018 21:16:45 GMT

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

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 — Fri, 05 Jan 2018 14:00:17 GMT

Could you please add the expect output to compare?

Re: Issue with PEEK and Rank duplicating rows

Anonymous — Fri, 05 Jan 2018 15:04:03 GMT

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_ — Sun, 14 Jan 2018 04:44:20 GMT

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_ — Sun, 14 Jan 2018 04:50:59 GMT

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