Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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.
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;
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.