Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 !
How the date_validation and date_reception created?
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
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.
any sample document that demonstrate the resolution.