Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I am creating a demo application to demonstrate changes in patient care from admission to discharge. The patient can attend more than once so I am trying to give each row of data an occurrence ie.
Row_ID | Patient_ID | Admission_date | Occur |
1 | 11 | 04/01/2013 | 1 |
2 | 22 | 04/01/2013 | 1 |
3 | 33 | 04/01/2013 | 1 |
4 | 11 | 03/02/2013 | 2 |
5 | 33 | 04/02/2013 | 2 |
6 | 44 | 05/02/2013 | 1 |
7 | 55 | 06/02/2013 | 1 |
8 | 44 | 07/02/2013 | 2 |
9 | 11 | 08/02/2013 | 3 |
10 | 22 | 05/03/2013 | 2 |
I attempted to use firstsortedvalue.
LOAD Patient_ID,
if(FirstSortedValue(Patient_ID,Admission_date,1)<>null(),1) as Occur
FROM Data_sample.csv
GROUP BY Patient_ID;
LOAD Patient_ID,
if(FirstSortedValue(Patient_ID,Admission_date,2)<>null(),2) as Occur
FROM Data_sample.csv
GROUP BY Patient_ID;
LOAD Patient_ID,
if(FirstSortedValue(Patient_ID,Admission_date,3)<>null(),3) as Occur
FROM Data_sample.csv
GROUP BY Patient_ID;
This works but it creates a separate table. I tried using Row_ID, in the above loads but I come up with invalid expression. I would prefer to join this to the main table.
Ideally I would like to be able to produce a chart like this, with the dimension of the graph being correctly sorted.
I have attached my sample qlikview and data file, any help would be much appreciated.
Many thanks
Jat
You wrote :
"This works but it creates a separate table..."
when I download Your files and reload qvw it gives me one table main_Occur
.. You mean You want to join Main_Occur to Main_Data?
Thanks for your reply. My table structure looks like this:
Yes I want to join those two tables. But I believe it should be joined on Row_ID as Patient_ID is not unique.
so You want to calculate Occur by Patient_ID or by Row_ID ?:)
I want to calculate Occur by the Patient_ID.
Any ideas?