Flags

[ajsjoshua]

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

[ajsjoshua]

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.

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

[zhadrakas]

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

[ajsjoshua]

Dear Tim,

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

[zhadrakas]

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.

[pradosh_thakur · Accepted solution]

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;