# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
cancel
Showing results for
Did you mean:
Contributor II

## Re: How to create a data set.

Hi All,

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

3 Replies
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

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

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