topic Re: wrong association, so wrong result in QlikView

wrong association, so wrong result

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

Hi Experts,

I have 2 tables. Table1 has sales data of 3 years. Table2 is a calender data of 3 years. In both tables each year has 12 months data, ie
-Month field values:
1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
-Year field values:
2014, 2015, 2016

When both tables are linked by YEAR field, then sum(sales) shows 1200 in text object(when single year selected), which is correct. But the straight table shows 1200 instead of 100, which is wrong.

When both tables are linked by MONTH field, then sum(sales) shows 3600 in text object(when single year selected) instead of 1200 which is wrong. In straight table shows 1200 instead of 100 which is again wrong.

The above result can be seen in the attached app.

I see that when I link both tables using a concatenated key of Month & Year, then i get correct result ie 100 in the staight table. I am trying to understand why I should create concatenated key here.

Please help me to understand why this behaviour.

Thanks!!

Re: wrong association, so wrong result

awhitfield — Tue, 10 Jan 2017 15:54:05 GMT

Hi Suraj,

try changing your script to:

Main:
LOAD *
INLINE [
Year, Month, Sales
2014, 1, 100
2014, 2, 100
2014, 3, 100
2014, 4, 100
2014, 5, 100
2014, 6, 100
2014, 7, 100
2014, 8, 100
2014, 9, 100
2014, 10, 100
2014, 11, 100
2014, 12, 100
2015, 1, 100
2015, 2, 100
2015, 3, 100
2015, 4, 100
2015, 5, 100
2015, 6, 100
2015, 7, 100
2015, 8, 100
2015, 9, 100
2015, 10, 100
2015, 11, 100
2015, 12, 100
2016, 1, 100
2016, 2, 100
2016, 3, 100
2016, 4, 100
2016, 5, 100
2016, 6, 100
2016, 7, 100
2016, 8, 100
2016, 9, 100
2016, 10, 100
2016, 11, 100
2016, 12, 100
];

Re: wrong association, so wrong result

sunny_talwar — Tue, 10 Jan 2017 15:54:06 GMT

Why don't you use Month1 as your dimension and choose Month? If you use Month1 as your dimension, it looks good

Sales and Month are not linked properly (just as your mentioned) which is causing the trouble here:

Re: wrong association, so wrong result

surajap123 — Tue, 10 Jan 2017 16:21:42 GMT

Hi Sunny,

I understand that the Month1 field is giving correct result.

Infact, I created this example to understand the behaviour of association. When I create a concatenated key in both tables it is also giving correct result.

LOAD *,Year1 & '|' & Month1 as Key;

I wanted to know, GENERALLY how we can decide when to create a concatenated key and what fields to include in that??

Re: wrong association, so wrong result

surajap123 — Wed, 11 Jan 2017 12:19:38 GMT

Could anyone answer to my query?

Re: wrong association, so wrong result

Anonymous — Wed, 11 Jan 2017 13:42:12 GMT

Hi Suraj,

In order to decide the "KEY" you may need to understand the DB design as well as Business logic. QlikView will work based on the key fields.So before creating any data model go through the data.

It is just generic.

-Jai