How to create a data set.

[Anonymous]

Hi All,

Thanks for the reply.

here is one example

I have one table with Patient age and 2 other tables for Height and Weight.

I need to create a bar graph with Age group as dimension and count of BMI ranges.

sample img:

BMI formula:

Weight/ ((Height*Height)/10000)     since height is recorded in Centimeters.

BMI Calculation conditions.

if( Weight<0.26 or Weight>650 or Height<20 or Height>280,

    -1,   else calculate BMI

if age < 18 then Height Service date  and Weight  service date should be done within 1 year. else consider as -1

if age >18 and <25 then Date's should lie within 2 years

If age >25 and the value is recorded, then calculate BMI (don't have to check the dates.)

Age:

PatientID, Age

Height:

PatientID	Value	Done	Service_Date
1240208	55	1	14/02/2017 0:00
1240209	165	1	16/12/2015 0:00
1240207	165	1	16/12/2015 0:00
1240201	190	1	20/05/2015 0:00
1240206	175	1	5/02/2016 0:00
1240200	140	1	10/08/2017 0:00
1240203	140	1	21/01/2015 0:00

  Weight:

PatientID	Value	Done	Service_Date
1240208	2.5	1	14/02/2017 0:00
1240209	45	1	16/12/2016 0:00
1240207	75	1	16/12/2015 0:00
1240201	100	1	20/05/2015 0:00
1240206	100	1	5/02/2014 0:00
1240203	94	1	21/01/2015 0:00
1240200	45	1	24/10/2016 0:00

My solution for this is to Left join the tables and calculate BMI in the new Field by checking the above conditions.

Is there any solution to calculate the BMI Field without joining the tables.

Any help would be appreciated.

Thanks.

Shiva

[ogautier62]

Hi,

why don't you concatenate tables Age, Height and Weight ?

it's more efficient in Qlik to denormalize with patientID, weight, height,age same row

regards

[Anonymous]

Hi Gautier,

That was just an example and i do have more than hundred tables where each tables holds one measurement or medication.

Do you think concatenate is the efficient way of preparing the dataset.

Regards

Shiva

[ogautier62]

I think you get advantage with Qlik to denormalize :

it's better to have one table with hundred fields, than hundred tables with two fields

so in load :

a fact table with all fields

for each measure,

join (fact_table) load patientID, measure from your measure table'

remark : done and service date are property of patient, if not so a table for each measure

and then in your graph you can have your expression for BMI

regards