Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
shiva_sr
Contributor II
Contributor II

Re: How to create a data set.

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.JPG

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:

 

PatientIDValueDoneService_Date
124020855114/02/2017 0:00
1240209165116/12/2015 0:00
1240207165116/12/2015 0:00
1240201190120/05/2015 0:00
124020617515/02/2016 0:00
1240200140110/08/2017 0:00
1240203140121/01/2015 0:00

  Weight:

PatientIDValueDoneService_Date
12402082.5114/02/2017 0:00
124020945116/12/2016 0:00
124020775116/12/2015 0:00
1240201100120/05/2015 0:00
124020610015/02/2014 0:00
124020394121/01/2015 0:00
124020045124/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

3 Replies
ogautier62
Specialist II
Specialist II

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

shiva_sr
Contributor II
Contributor II
Author

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
Specialist II
Specialist II

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