Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist
Specialist

Table Association

I have the following four main tables:

Screen Shot 2016-03-26 at 01.12.56.png

The above key table comprise a key (relevant code) together with a description of the relevant code.

My model also has the following tables:

Screen Shot 2016-03-26 at 01.13.41.png

Screen Shot 2016-03-26 at 01.15.29.png

The above table is called WHOEML

The last table is called FDATherapy and is as follows:

Screen Shot 2016-03-26 at 01.17.33.png

What would be the best method to associate these tables with no synthetic keys? As mentioned the first four tables are the key tables with the descriptions of the codes.

Regards

Chris

Tags (1)
1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: Table Association

Just something to start with (since the data model should also be designed to support your analysis best):

1) JOIN or ApplyMap the four Code tables to DimProduct (hence these four tables are removed from the data model afterwards)

2) Create a key field in DimProduct from the four Codes, e.g. using AutonumberXXX()

3) Create a key field in WHOEML table from the Code fields, then remove the Code fields from that table

4) Create a key field in FDATherapy table from the Code fields, then remove the Code fields from that table

Now you should have three tables, DimProduct, WHOEML and FDATherapy linked by the key created from Codes

Hope this helps,

Stefan

View solution in original post

7 Replies
Highlighted
MVP
MVP

Re: Table Association

Just something to start with (since the data model should also be designed to support your analysis best):

1) JOIN or ApplyMap the four Code tables to DimProduct (hence these four tables are removed from the data model afterwards)

2) Create a key field in DimProduct from the four Codes, e.g. using AutonumberXXX()

3) Create a key field in WHOEML table from the Code fields, then remove the Code fields from that table

4) Create a key field in FDATherapy table from the Code fields, then remove the Code fields from that table

Now you should have three tables, DimProduct, WHOEML and FDATherapy linked by the key created from Codes

Hope this helps,

Stefan

View solution in original post

Highlighted
Specialist
Specialist

Re: Table Association

Thanks Stefan

What would be the 'search value(s)' in the source table?

Regards

Highlighted
MVP
MVP

Re: Table Association

Not sure what you mean with that. Are you referring to my proposal?

Which part of it?

Highlighted
Specialist
Specialist

Re: Table Association

Yes, let me try it out and will come back to you

Highlighted
Specialist
Specialist

Re: Table Association

Thanks Stefan

Model is now as follows:

Screen Shot 2016-03-26 at 04.34.43.png

The other 4 tables have been dropped as i used ApplyMap.  However, in one of my chart reports i get the following:

Screen Shot 2016-03-26 at 04.35.56.png

I am not sure why i get the blank row under the Anatomical Main Group field (4TH ROW).  Also do not want the 'UNCLASSIFIED/NON-PHARMACEUTICALS IN THE LAST ROW TO APPEAR IN THE CHART.

REGARDS

Highlighted
MVP
MVP

Re: Table Association

You probably get a blank dimension in one row because your measure is not linked to Anatomical Main Group in some cases.

On the other hand, the last row shows zero for UNCLASSIFIED, because you don't have any entries in your measure table for this dimension value.

Does this make sense?

If appropriate you can filter these lines out using 'Suppress zero values' on presentation tab and 'Suppress When NULL' on dimension tab. Or you might want to find out why there is no relation in your data for these key values.

Regards,

Stefan

Highlighted
Specialist
Specialist

Re: Table Association

Thanks Stefan

I was doing some troubleshooting in order to find out the root cause of the blanks.  I ran the model for the specify chart which had a blank and left our other irrelevant tables as follows:

Screen Shot 2016-03-27 at 18.09.20.png

The two tables are as follows

Screen Shot 2016-03-27 at 17.27.13.png

Screen Shot 2016-03-27 at 17.28.09.png

The report which i ran is as follows:

Screen Shot 2016-03-27 at 17.50.17.png

I then realised that the blank in the full report as given above where we used ApplyMap, was coming from the red circled Anatomical Main Group 'ANTIPARASITIC PRODUCTS, INSECTICIDES AND REPELLENTS', see below:

Screen Shot 2016-03-27 at 17.50.35.png

The above shows the blank in the full model.  So what this means is that the association done is not working as it should be.

Regards