Yearly Target values not mapped to Fact table

[divya_anand]

Hello,

I am trying to link yearly targets to the main data set in QlikView. The issue is that - In the 1st picture, you can see that the sum of target for Name=A for 2018 is 6. However, in the main data set (picture 2) there is no Code for Plant =A & Group = S, M. Hence, the respective targets are not considered in QlikView. 

I found 2 solutions on community:

1. Concatenate : This doesn't work because the actual date is connected to the calendar,  whereas the targets are on yearly basis

2. Add Dummy data: It is not possible for me to add dummy data based on missing Codes because it is dynamic

Any other idea how I can make this work? Attaching a Sample QVW.

Thank you.

[Mark_Little]

HI,

Have you tried a join.

LOAD * from table1;

join

LOAD * from table2;

Mark

[divya_anand]

Hi,

I tried "Join", it still isn't picking up all the target values. I have attached a sample QVW in my question for your reference.

Thank you.

[sunny_talwar · Accepted solution]

May be using a link table

Data:
LOAD Name,
	 Group,
	 Code,
	 Name&'|'&Group&'|'&Date#(Date,'DD/MM/YYYY') as Key,
	 Num(Date#(Date,'DD/MM/YYYY')) as %Date;
LOAD * INLINE [
    Name, Group, Code, Date
    A, K, 308, 15/1/2018
    A, K, 309, 26/2/2018
    A, K, 314, 18/2/2018
    A, K, 493, 16/3/2018
    B, K, 494, 25/1/2018
    B, K, 496, 13/1/2018
    B, K, 497, 17/3/2018
    C, M, 316, 27/4/2018
    C, M, 490, 20/2/2018
    C, S, 306, 13/1/2018
    A, K, 202, 18/8/2017
    C, S, 209, 22/8/2017
];

Target:
LOAD Name&'|'&Group&'|'&Year as Key,
	 Name,
	 Group,
	 Year,
	 Target;
LOAD * INLINE [
    Name, Group, Year, Target
    A, K, 2018, 1
    A, S, 2018, 2
    A, M, 2018, 3
    B, K, 2018, 2
    B, S, 2018, 1
    B, M, 2018, 1
    C, K, 2018, 2
    C, S, 2018, 3
    C, M, 2018, 4
    A, K, 2017, 1
    A, S, 2017, 2
    A, M, 2017, 3
    C, K, 2017, 2
    C, S, 2017, 2
    C, M, 2017, 3
];

LinkTable:
LOAD Name,
	 Group,
	 %Date,
	 Key
Resident Data;

Concatenate (LinkTable)
LOAD Name,
	 Group,
	 MakeDate(Year) as %Date,
	 Key
Resident Target;

DROP Fields Name, Group, %Date from Data;
DROP Fields Name, Group, Year from Target;

[divya_anand]

Hi Sunny,

Thank you very much.

This should work. I am trying to implement this logic on my production report, I will get back to you.

[divya_anand]

Hi Sunny,
Works well, thank you very much.