topic Re: Flags in QlikView

Flags

ajsjoshua — Wed, 25 Nov 2020 16:16:04 GMT

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.

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

Re: Flags

zhadrakas — Wed, 17 Jan 2018 07:21:08 GMT

can you share a sample qvw for this?

Re: Flags

siddharth_s3 — Wed, 17 Jan 2018 07:21:51 GMT

Did you sort your data by patient ID?

Peek just looks at the previous row.

Re: Flags

Anil_Babu_Samineni — Wed, 17 Jan 2018 07:24:21 GMT

Perhaps this?

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

Re: Flags

ajsjoshua — Wed, 17 Jan 2018 07:44:24 GMT

Dear Tim,

PFA for qvw.

Regards,

Joshua.

Re: Flags

ajsjoshua — Wed, 17 Jan 2018 07:46:56 GMT

Dear Siddharth,

Yes based on Date .

Re: Flags

zhadrakas — Wed, 17 Jan 2018 07:51:12 GMT

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);

Re: Flags

ajsjoshua — Wed, 17 Jan 2018 07:52:32 GMT

Dear Anil,

I am sorting the data based on Patient.

Re: Flags

ajsjoshua — Wed, 17 Jan 2018 08:03:06 GMT

Dear Tim,

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

I am getting 5 but i want 2.

Re: Flags

siddharth_s3 — Wed, 17 Jan 2018 08:08:55 GMT

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.

Re: Flags

ajsjoshua — Wed, 17 Jan 2018 08:22:24 GMT

Dear all,

By using the script suggested by Tim and using using this expression

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

I am getting 5 but my output should be 2 because only 2 patients i.e patientid 1 and 4 revisited again.

Re: Flags

zhadrakas — Wed, 17 Jan 2018 08:26:33 GMT

i think that QlikView still read the sort order from the first load Statement:

can you Change your Excel thats it is listed like this and please test it again.

Then you know if the sort order is the Problem.

patientid	type	Date	revisit	revenue	discount
1	Op	02.03.2017	0	5000	250
1	Op	01.05.2017	0	3000	150
2	Op	05.03.2017	0	4500	0
3	Op	18.04.2017	0	6000	0
4	Op	23.04.2017	0	7000	350
4	Op	02.05.2017	0	5000	250
5	Op	01.05.2017	0	2000	0

Re: Flags

ajsjoshua — Wed, 17 Jan 2018 08:30:51 GMT

Dear Tim,

In this case i am fetching from excel but actually i am fetching from qvd for my app.

Re: Flags

zhadrakas — Wed, 17 Jan 2018 08:33:57 GMT

then please read from an Excel that is sorted right like i posted above to test if the sort order from your QVD is the Problem here.

Re: Flags

pradosh_thakur — Wed, 17 Jan 2018 09:11:16 GMT

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;