Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

jatishqv
New Contributor II

FirstSortedValue to define occurrence

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_IDPatient_IDAdmission_dateOccur
11104/01/20131
22204/01/20131
33304/01/20131
41103/02/20132
53304/02/20132
64405/02/20131
75506/02/20131
84407/02/20132
91108/02/20133
102205/03/20132

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.qv_app.jpg

I have attached my sample qlikview and data file, any help would be much appreciated.

Many thanks

Jat

5 Replies
Not applicable

Re: FirstSortedValue to define occurrence

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
New Contributor II

Re: FirstSortedValue to define occurrence

Thanks for your reply. My table structure looks like this:

tbl.jpg

Yes I want to join those two tables. But I believe it should be joined on Row_ID as Patient_ID is not unique.

Not applicable

Re: FirstSortedValue to define occurrence

so You want to calculate Occur by Patient_ID or by Row_ID ?Smiley Happy

jatishqv
New Contributor II

Re: FirstSortedValue to define occurrence

I want to calculate Occur by the Patient_ID.

jatishqv
New Contributor II

Re: FirstSortedValue to define occurrence

Any ideas?

Community Browser