FirstSortedValue to define occurrence

[jatishqv]

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

[#2]

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?

[jatishqv]

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.

[#4]

so You want to calculate Occur by Patient_ID or by Row_ID ?:)

[jatishqv]

I want to calculate Occur by the Patient_ID.

[jatishqv]

Any ideas?