Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Nanou
Contributor
Contributor

Aggregate the data properly

Hello, 

The question is gonna be a bit messy as I might have made everything more complicated than needed.

Basically I have a table "patient_step" with a form_id, form_num, a patient_id and dates of creation, update, delete

And originally a big table with the forms that would go : form_id, form_input, form_value. Form_num is used because the table contains multiple different forms (entry, exit, and dozens others, 5 of which we'll use)

Here i made modifications to have the form_inputs as columns and and form_values as values with something like


LOAD
%form_id,
maxstring(if(Form_inputid = 'customRadio1', Form_instance_data.displayablevalue)) as customRadio1,
maxstring(if(Form_inputid = 'customRadio2', Form_instance_data.displayablevalue)) as customRadio2,
maxstring(if(Form_inputid = 'customRadio3', Form_instance_data.displayablevalue)) as customRadio3,
Date#(maxstring(if(Form_inputid = 'Date_entry', Form_instance_data.displayablevalue))) as Date_EntryFROM [lib://LOCAL_QVD/Form_instance_data.qvd](qvd)
WHERE ApplyMap('Map_FormID_FormNum', %form_id) = '1'
GROUP BY %form_id ;

The fields are anonymised, there's obviously a few more lines and the 4 other forms !

Essentially getting one line per form there and clear column names, cool ! But now... my issue is that ultimately I would actually need one line per patient_ID to avoid this issue : (example with two dates that are originally from different forms)

Nanou_0-1732009844712.png

as well as be able to calculate things between forms (for instance time passing between those two dates)

I'm sorry I cant really provide the data set but if it's not clear I can try to explain better !

Thanks a lot !

Labels (2)
4 Replies
Anil_Babu_Samineni

How the date_validation and date_reception created? 

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Nanou
Contributor
Contributor
Author

They are "transposed" from our big forms table like shown in the script; they have a different form_ID but are linked to the same patient_ID

Qrishna
Master
Master

Seeing the picture attached, i see theres no correlation betwen date_validation and date_reception hence 2 different lines are created. but i can wrong though as i have no idea whats you date looklike.

 

try using Aggr(expression_with_date_reception, %form.patientid)

if that doesnt work provide the expression you using in both date columns.

Anil_Babu_Samineni

any sample document that demonstrate the resolution. 

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful