Aggregate the data properly

[Nanou]

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)

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 !

[Anil_Babu_Samineni]

How the date_validation and date_reception created? 

[Nanou]

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]

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.