wrong association, so wrong result

[surajap123]

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!!

[awhitfield]

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
];

[sunny_talwar]

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:

[surajap123]

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??

[surajap123]

Could anyone answer to my query?

[Anonymous]

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