Readmission not picking right peek date

[Anonymous]

I am working on my readmission report and currently am not getting the right visits. Every patients have UniqueNumber for all the visits. They get new Vid on every visit they make. Here is my code that needs to be modified:

Dimension: UniqueNumber and Vid

Expression: AdmitDate and DischargeDate

Visits:

SQL select UniqueNumber,

    Vid,

    AdmitDate,

    DischargeDate

from Visits

LEFT JOIN(Visits)

Load

*, ReadmitAdmitDate-DischargeDate as diff;

LOAD

*,

    Peek(Vid) as ReadmitVid,

    Peek(AdmitDate) as ReadmitAdmitDate,

    Peek(DischargeDate) as ReadmitDischargeDate

RESIDENT Visits

Order by UniqueNumber,Vid desc;

Data in database:

UniqueNumber	Vid	AdmitDate	DischargeDate
1001	111	12/12/2015	12/23/2015
2001	222	1/6/2016	1/9/2016
2001	333	1/23/2016

Current wrong result: UniqueNumber 1001 picked 2001 initial vid as ReadmitVid.

UniqueNumber	ReadmitVid	ReadmitAdmitDate	InitialVid	InitialAdmitDate	InitialDischargeDate
2001	333	1/23/2016	222	1/6/2016	1/9/2016
1001	222	1/6/2016	111	12/12/2015	12/23/2015

Intended result:

UniqueNumber	ReadmitVid	ReadmitAdmitDate	InitialVid	InitialAdmitDate	InitialDischargeDate
2001	333	1/23/2016	222	1/6/2016	1/9/2016

Since UniqueNumber 1001 did not have a second visit, it should not get in the intended result.

Any help? What am I doing wrong?

Thanks.

[sinanozdemir · Accepted solution]

Hi,

Maybe try to put your Peek() in an IF statement:

LEFT JOIN(Visits)

Load

*, ReadmitAdmitDate-DischargeDate as diff;

LOAD

*,

  If(UniqueNumber = Peek(UniqueNumber), Peek(Vid)) as ReadmitVid,

  If(UniqueNumber = Peek(UniqueNumber), Peek(AdmitDate)) as ReadmitAdmitDate,

  If(UniqueNumber = Peek(UniqueNumber),Peek(DischargeDate)) as ReadmitDischargeDate

RESIDENT Visits

Order by UniqueNumber,Vid desc;

As a result of this, you would only pick the previous Vids, AdmitDates, and DischargeDates if the UniqueNumber repeats.

Hope this helps.

Other useful links by Sinan:

Handling Metadata in QlikView

QlikView and Python Integration

QlikView Automation Series – Unzip & Load Excel Files:

[Anonymous]

That did the trick. Thank you. I don't get an option to mark your post as an answer if you can mark it by yourself.

[Anonymous]

Hi Vishal,

You can mark replies as correct by clicking the "Correct Answer" button at the bottom of the post.

Please see this thread for more details: Qlik Community Tip: Marking Replies as Correct or Helpful

Please let me know if you have any questions.

Best regards,

Qlik Community Moderation Team