Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
ajsjoshua
Valued Contributor

Flags

Dear all,

I am calculating the patients who are visited again and so i had created flag in script and called in set analysis.

If(Peek(patientid)=patientid,1,0) as revisit

=count({<revisit={1},Year={'$(=max(Year))'}>} patientid)

But i am not getting the answer. In this case my answer should be 2.

 

patientidtypedaterevenuediscount
1Op2/3/20175000250
2Op5/3/201745000
3Op18/4/201760000
4Op23/4/20177000350
5Op1/5/201720000
1Op1/5/20173000150
4Op2/5/20175000250
Tags (1)
1 Solution

Accepted Solutions
Highlighted
pradosh_thakur
Honored Contributor II

Re: Flags

Hi Joshua

i tweaked the script a bit. Expression is same as yours

PFA

ABC:

LOAD *

INLINE [

    patientid, type, date, revenue, discount

    1, Op, 2/3/2017, 5000, 250

    2, Op, 5/3/2017, 4500, 0

    3, Op, 18/4/2017, 6000, 0

    4, Op, 23/4/2017, 7000, 350

    5, Op, 1/5/2017, 2000, 0

    1, Op, 1/5/2017, 3000, 150

    4, Op, 2/5/2017, 5000, 250

];

NoConcatenate

Load

patientid,

type,

date,

revenue,

discount,

If(patientid=Previous(patientid),1,0) as revisit

RESIDENT ABC

ORDER BY patientid ASC;

drop table ABC;

Learning never stops.

View solution in original post

14 Replies
Highlighted
zhadrakas
Valued Contributor II

Re: Flags

can you share a sample qvw for this?

Highlighted
Partner
Partner

Re: Flags

Did you sort your data by patient ID?

Peek just looks at the previous row.

Highlighted

Re: Flags

Perhaps this?

If(patientid=Previous(patientid),1,0) as revisit // But, In your data there is not common with current to previous

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
ajsjoshua
Valued Contributor

Re: Flags

Dear Tim,

PFA for qvw.

Regards,

Joshua.

Highlighted
ajsjoshua
Valued Contributor

Re: Flags

Dear Siddharth,

Yes based on Date .

Highlighted
zhadrakas
Valued Contributor II

Re: Flags

i think your data needed to be sorted by Patient iD like Siddarth said.

try this script to test that:

patient:
Load
patientid,
type,
Date,
If(patientid=Previous(patientid),1,0) as revisit,
revenue,
discount
;
Load
patientid,
type,
Date,
revenue,
discount
ORDER BY patientid ASC
;
LOAD patientid,
type,
date as Date,
revenue,
discount
FROM
E:\Qlik\healthcare\Sukra\samp.xlsx
(
ooxml, embedded labels, table is Sheet1);

Highlighted
ajsjoshua
Valued Contributor

Re: Flags

Dear Anil,

I am sorting the data based on Patient.

Highlighted
ajsjoshua
Valued Contributor

Re: Flags

Dear Tim,

By using this script and expression =count({<revisit={1},Year={'$(=max(Year))'}>} patientid)

I am getting 5 but i want 2.

Highlighted
Partner
Partner

Re: Flags

Sort it by patient id and then date.

Unless there is a reason you are sorting it by date first.

You would have to try a different approach if you have to sort the data by date first.