Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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);
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 |
Dear Tim,
In this case i am fetching from excel but actually i am fetching from qvd for my app.
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.
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;