topic Re: How to create a data set. in QlikView

How to create a data set.

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am new to Qlik and need some help about creating a data set for the following scenario.

i have a patient table and each related information is stored in different tables like age, smoking, Medication, etc..

i neeed to create a report like

1. how many patients do not have Smoking recorded (if a patientId does not exist in Smoking table, he should be considered as not recorded)?

2. how many patients with age<18 and smoking not recorded?

3. How many patients have both Smoking and Medication recorded?

My solution was to Left join Patient table with all the remaining tables age, Smoking, Medication and so on.

But i have hundreds of tables and the Patient table will be huge.

Can someone help me with best possible solution in this scenario.

Patient:

Load * Inline

[

Id, Name

1, aa

2, bb

3, cc

4, dd

5, ee

6, ff

7, gg

8, hh

9, ii

10, jj

11, kk

];

Age:

Load * Inline

[

ID, Age, Sex

2, 5, F

3, 10, M

5, 35, M

7, 20, M

8, 25, F

];

Smoking:

Load * Inline

[

ID, SmokingCondition, Date

5, 9

6, 5

7, 1

8, 2

];

Medication:

Load * Inline [

ID, Medication, Date

1, yes, 10/02/2015

2, No,

3, No,

4, Yes, 15/05/2017

5, Yes, 20/12/2016

];

Re: How to create a data set.

adityaakshaya — Thu, 21 Jun 2018 04:57:40 GMT

Hi Shiva,

Rather doing the left join, I will suggest to concatenate all the attributes tables like Medication, smoking and like other in one with flag defining which is smoking and which is medication.

You can left join age table with patient table so that Patient age will be part of Patient table.

Let me now if you need any further clarification.

Regards,

Akshaya

Re: How to create a data set.

jyothish8807 — Thu, 21 Jun 2018 04:58:47 GMT

Hi Raju,

Pfa sample:

Br,

Re: How to create a data set.

adityaakshaya — Thu, 21 Jun 2018 05:03:38 GMT

Hi Jyothish,

Considering Patient name and age having 1 : 1 relation and as Patient table is huge and he has 100 of tables, it is better to have age table part of Patient table itself.

Regards,

Akshaya

Re: How to create a data set.

albertovarela — Thu, 21 Jun 2018 05:12:29 GMT

The key is having only one common field in all tables (field name must be identical ~ case sensitive) to build your model.

Please find attached qvw.

Re: How to create a data set.

jyothish8807 — Thu, 21 Jun 2018 06:00:33 GMT

Yes, doing a join would be a better approach, it will also result in a less complex model