Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
star_limit
Creator II
Creator II

Readmission not picking right peek date

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:

UniqueNumberVidAdmitDateDischargeDate
100111112/12/201512/23/2015
20012221/6/20161/9/2016
20013331/23/2016

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

UniqueNumberReadmitVidReadmitAdmitDateInitialVidInitialAdmitDateInitialDischargeDate
20013331/23/20162221/6/20161/9/2016
10012221/6/201611112/12/201512/23/2015

Intended result:

UniqueNumberReadmitVidReadmitAdmitDateInitialVidInitialAdmitDateInitialDischargeDate
20013331/23/20162221/6/20161/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.

1 Solution

Accepted Solutions
sinanozdemir
Specialist III
Specialist III

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:

View solution in original post

3 Replies
sinanozdemir
Specialist III
Specialist III

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:

star_limit
Creator II
Creator II
Author

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
Not applicable

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