Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
I am relatively new in Qlik.
I have to perform the following calcualtion between two tables in the data load editor script,
Table1:
Cat2 | Year | Qty |
AA | 2020 | 16,0 |
AA | 2021 | 11,0 |
AA | 2022 | 21,0 |
AA | 2023 | 24,0 |
AA | 2024 | 22,0 |
AA | 2025 | 5,0 |
AA | 2026 | 1,0 |
AA | 2027 | 2,0 |
AA | 2028 | 18,0 |
AA | 2029 | 24,0 |
AB | 2020 | 23,0 |
AB | 2021 | 18,0 |
AB | 2022 | 15,0 |
AB | 2023 | 12,0 |
AB | 2024 | 12,0 |
AB | 2025 | 10,0 |
AB | 2026 | 11,0 |
AB | 2027 | 14,0 |
AB | 2028 | 3,0 |
AB | 2029 | 13,0 |
Table2:
Cat2 | Year | Y20 | Y21 | Y22 | Y23 | Y24 | Y25 | Y26 | Y27 | Y28 | Y29 |
AA | 2020 | 9,00 | 0,00 | 0,00 | 0,00 | 0,00 | 0,00 | 0,00 | 0,00 | 0,00 | 0,00 |
AA | 2021 | 4,00 | 8,00 | 0,00 | 0,00 | 0,00 | 0,00 | 0,00 | 0,00 | 0,00 | 0,00 |
AA | 2022 | 4,00 | 4,00 | 7,00 | 0,00 | 0,00 | 0,00 | 0,00 | 0,00 | 0,00 | 0,00 |
AA | 2023 | 8,00 | 2,00 | 2,00 | 3,00 | 0,00 | 0,00 | 0,00 | 0,00 | 0,00 | 0,00 |
AA | 2024 | 1,00 | 7,00 | 9,00 | 4,00 | 4,00 | 0,00 | 0,00 | 0,00 | 0,00 | 0,00 |
AA | 2025 | 3,00 | 7,00 | 3,00 | 5,00 | 7,00 | 2,00 | 0,00 | 0,00 | 0,00 | 0,00 |
AA | 2026 | 7,00 | 9,00 | 4,00 | 4,00 | 2,00 | 1,00 | 3,00 | 0,00 | 0,00 | 0,00 |
AA | 2027 | 2,00 | 9,00 | 7,00 | 3,00 | 4,00 | 5,00 | 7,00 | 7,00 | 0,00 | 0,00 |
AA | 2028 | 9,00 | 2,00 | 2,00 | 5,00 | 5,00 | 5,00 | 4,00 | 8,00 | 0,00 | 0,00 |
AA | 2029 | 4,00 | 4,00 | 5,00 | 5,00 | 6,00 | 5,00 | 6,00 | 9,00 | 5,00 | 0,00 |
AB | 2020 | 4,00 | 0,00 | 0,00 | 0,00 | 0,00 | 0,00 | 0,00 | 0,00 | 0,00 | 0,00 |
AB | 2021 | 8,00 | 1,00 | 0,00 | 0,00 | 0,00 | 0,00 | 0,00 | 0,00 | 0,00 | 0,00 |
AB | 2022 | 2,00 | 9,00 | 5,00 | 0,00 | 0,00 | 0,00 | 0,00 | 0,00 | 0,00 | 0,00 |
AB | 2023 | 5,00 | 9,00 | 7,00 | 8,00 | 0,00 | 0,00 | 0,00 | 0,00 | 0,00 | 0,00 |
AB | 2024 | 9,00 | 5,00 | 1,00 | 6,00 | 6,00 | 0,00 | 0,00 | 0,00 | 0,00 | 0,00 |
AB | 2025 | 7,00 | 6,00 | 9,00 | 4,00 | 7,00 | 6,00 | 0,00 | 0,00 | 0,00 | 0,00 |
AB | 2026 | 2,00 | 7,00 | 5,00 | 1,00 | 6,00 | 6,00 | 5,00 | 0,00 | 0,00 | 0,00 |
AB | 2027 | 1,00 | 4,00 | 4,00 | 3,00 | 6,00 | 4,00 | 4,00 | 6,00 | 0,00 | 0,00 |
AB | 2028 | 1,00 | 3,00 | 8,00 | 2,00 | 9,00 | 4,00 | 3,00 | 4,00 | 0,00 | 0,00 |
AB | 2029 | 8,00 | 6,00 | 5,00 | 5,00 | 3,00 | 3,00 | 2,00 | 1,00 | 9,00 | 0,00 |
Now I want to have a field 'AggrQty' in Table 1
AggrQty (Cat2=AA,Year=2020)=Qty(Cat2=AA,Year=2020)* Y20(Cat2=AA,Year=2020)
+Qty(Cat2=AA,Year=2021)* Y21(Cat2=AA,Year=2020)
+Qty(Cat2=AA,Year=2022)* Y22(Cat2=AA,Year=2020)
+..................
+Qty(Cat2=AA,Year=2029)* Y29(Cat2=AA,Year=2020)
And the same for other Cat2 and years in Table1
Thank you for your suggestion
Hi,
If I understood correctly, one solution :
I kept the code long enough to explain the logic
Table1:
LOAD Cat2 as C ,Year as Y, Qty as Q INLINE [
Cat2 ,Year, Qty
AA, 2020, 16
AA, 2021, 11
AA, 2022, 21
AA, 2023, 24
AA, 2024, 22
AA, 2025, 5
AA, 2026, 1
AA, 2027, 2
AA, 2028, 18
AA, 2029, 24
AB, 2020, 23
AB, 2021, 18
AB, 2022, 15
AB, 2023, 12
AB, 2024, 12
AB, 2025, 10
AB, 2026, 11
AB, 2027, 14
AB, 2028, 3
AB, 2029, 13
];
TableTmp:
CrossTable(YearNum, Data, 2)
LOAD * INLINE [
Cat2, Year, Y20, Y21, Y22, Y23, Y24, Y25, Y26, Y27, Y28, Y29
AA, 2020, 9, 0, 0, 0, 0, 0, 0, 0, 0, 0
AA, 2021, 4, 8, 0, 0, 0, 0, 0, 0, 0, 0
AA, 2022, 4, 4, 7, 0, 0, 0, 0, 0, 0, 0
AA, 2023, 8, 2, 2, 3, 0, 0, 0, 0, 0, 0
AA, 2024, 1, 7, 9, 4, 4, 0, 0, 0, 0, 0
AA, 2025, 3, 7, 3, 5, 7, 2, 0, 0, 0, 0
AA, 2026, 7, 9, 4, 4, 2, 1, 3, 0, 0, 0
AA, 2027, 2, 9, 7, 3, 4, 5, 7, 7, 0, 0
AA, 2028, 9, 2, 2, 5, 5, 5, 4, 8, 0, 0
AA, 2029, 4, 4, 5, 5, 6, 5, 6, 9, 5, 0
AB, 2020, 4, 0, 0, 0, 0, 0, 0, 0, 0, 0
AB, 2021, 8, 1, 0, 0, 0, 0, 0, 0, 0, 0
AB, 2022, 2, 9, 5, 0, 0, 0, 0, 0, 0, 0
AB, 2023, 5, 9, 7, 8, 0, 0, 0, 0, 0, 0
AB, 2024, 9, 5, 1, 6, 6, 0, 0, 0, 0, 0
AB, 2025, 7, 6, 9, 4, 7, 6, 0, 0, 0, 0
AB, 2026, 2, 7, 5, 1, 6, 6, 5, 0, 0, 0
AB, 2027, 1, 4, 4, 3, 6, 4, 4, 6, 0, 0
AB, 2028, 1, 3, 8, 2, 9, 4, 3, 4, 0, 0
AB, 2029, 8, 6, 5, 5, 3, 3, 2, 1, 9, 0
];
Table2:
noconcatenate
load Cat2,Year as Year2, (2000+Num(right(YearNum,2))) as Year,Data resident TableTmp;
drop table TableTmp;
right join
load C as Cat2 ,Y as Year , Q as Qty resident Table1;
New_Table1:
noconcatenate
load Cat2,Year2 as Year,sum(Qty*Data) as AggrQty resident Table2 group by Cat2, Year2 ;
right join
load C as Cat2 ,Y as Year , Q as Qty resident Table1;
drop table Table2,Table1;
output :
Other Category field should not affect the current solution which I had provided earlier with bit of modification
// Add other category fields here as highlighted . You can add any you want
T1:
LOAD
Cat2,
Cat3,
Cat4,
year(Date#("Year",'YYYY')) as Year1,
Qty
FROM [lib://Qlik]
(html, utf8, embedded labels, table is @1);
Left Join
Load Distinct Year1 as Year
Resident T1;
T2:
CrossTable(Year2,Qty2,2)
LOAD
Cat2,
"Year",
Y20,
Y21,
Y22,
Y23,
Y24,
Y25,
Y26,
Y27,
Y28,
Y29
FROM [lib://Qlik]
(html, utf8, embedded labels, table is @2);
Left join(T1)
Load Distinct Cat2,
year(Date#("Year",'YYYY')) as Year,
year(Date#(right(Year2,2),'YY')) as Year1,
Qty2
Resident T2;
Drop Tables T2;
// You need to include all category field in final load and group by statement as well
T3:
Load Cat2,
Year,
Cat3,
Cat4,
sum(if(Year1=Year,Qty)) as Qty,
sum(Qty*Qty2) as AggrQty
Resident T1
Group by Cat2,Cat3,Cat4,Year;
Drop Table T1;
Hi,
If I understood correctly, one solution :
I kept the code long enough to explain the logic
Table1:
LOAD Cat2 as C ,Year as Y, Qty as Q INLINE [
Cat2 ,Year, Qty
AA, 2020, 16
AA, 2021, 11
AA, 2022, 21
AA, 2023, 24
AA, 2024, 22
AA, 2025, 5
AA, 2026, 1
AA, 2027, 2
AA, 2028, 18
AA, 2029, 24
AB, 2020, 23
AB, 2021, 18
AB, 2022, 15
AB, 2023, 12
AB, 2024, 12
AB, 2025, 10
AB, 2026, 11
AB, 2027, 14
AB, 2028, 3
AB, 2029, 13
];
TableTmp:
CrossTable(YearNum, Data, 2)
LOAD * INLINE [
Cat2, Year, Y20, Y21, Y22, Y23, Y24, Y25, Y26, Y27, Y28, Y29
AA, 2020, 9, 0, 0, 0, 0, 0, 0, 0, 0, 0
AA, 2021, 4, 8, 0, 0, 0, 0, 0, 0, 0, 0
AA, 2022, 4, 4, 7, 0, 0, 0, 0, 0, 0, 0
AA, 2023, 8, 2, 2, 3, 0, 0, 0, 0, 0, 0
AA, 2024, 1, 7, 9, 4, 4, 0, 0, 0, 0, 0
AA, 2025, 3, 7, 3, 5, 7, 2, 0, 0, 0, 0
AA, 2026, 7, 9, 4, 4, 2, 1, 3, 0, 0, 0
AA, 2027, 2, 9, 7, 3, 4, 5, 7, 7, 0, 0
AA, 2028, 9, 2, 2, 5, 5, 5, 4, 8, 0, 0
AA, 2029, 4, 4, 5, 5, 6, 5, 6, 9, 5, 0
AB, 2020, 4, 0, 0, 0, 0, 0, 0, 0, 0, 0
AB, 2021, 8, 1, 0, 0, 0, 0, 0, 0, 0, 0
AB, 2022, 2, 9, 5, 0, 0, 0, 0, 0, 0, 0
AB, 2023, 5, 9, 7, 8, 0, 0, 0, 0, 0, 0
AB, 2024, 9, 5, 1, 6, 6, 0, 0, 0, 0, 0
AB, 2025, 7, 6, 9, 4, 7, 6, 0, 0, 0, 0
AB, 2026, 2, 7, 5, 1, 6, 6, 5, 0, 0, 0
AB, 2027, 1, 4, 4, 3, 6, 4, 4, 6, 0, 0
AB, 2028, 1, 3, 8, 2, 9, 4, 3, 4, 0, 0
AB, 2029, 8, 6, 5, 5, 3, 3, 2, 1, 9, 0
];
Table2:
noconcatenate
load Cat2,Year as Year2, (2000+Num(right(YearNum,2))) as Year,Data resident TableTmp;
drop table TableTmp;
right join
load C as Cat2 ,Y as Year , Q as Qty resident Table1;
New_Table1:
noconcatenate
load Cat2,Year2 as Year,sum(Qty*Data) as AggrQty resident Table2 group by Cat2, Year2 ;
right join
load C as Cat2 ,Y as Year , Q as Qty resident Table1;
drop table Table2,Table1;
output :
another approach
T1:
LOAD
Cat2,
year(Date#("Year",'YYYY')) as Year1,
Qty
FROM [lib://Qlik]
(html, utf8, embedded labels, table is @1);
Left Join
Load Distinct Year1 as Year
Resident T1;
T2:
CrossTable(Year2,Qty2,2)
LOAD
Cat2,
"Year",
Y20,
Y21,
Y22,
Y23,
Y24,
Y25,
Y26,
Y27,
Y28,
Y29
FROM [lib://Qlik]
(html, utf8, embedded labels, table is @2);
Left join(T1)
Load Distinct Cat2,
year(Date#("Year",'YYYY')) as Year,
year(Date#(right(Year2,2),'YY')) as Year1,
Qty2
Resident T2;
Drop Tables T2;
T3:
Load Cat2,
Year,
sum(if(Year1=Year,Qty)) as Qty,
sum(Qty*Qty2) as AggrQty
Resident T1
Group by Cat2,Year;
Drop Table T1;
Hello Taofiq,
Thanks for this neat solution.
I do have a problem though...I did not add this in the tables sample as though would not be very relevant.
But I see, some change might be needed in the soution..
The problem is Table1 has many more dimensions like Cat2..........for example Cat3 Cat4 Cat5...........Cat12.......Qty value corresponds to all of those dimensions.
But Table 2 has only Cat2 and Year common dimensions....
Multiplying is not problem....but then Sum(Qty*Data) as AggrQty would not create problem?
Other Category field should not affect the current solution which I had provided earlier with bit of modification
// Add other category fields here as highlighted . You can add any you want
T1:
LOAD
Cat2,
Cat3,
Cat4,
year(Date#("Year",'YYYY')) as Year1,
Qty
FROM [lib://Qlik]
(html, utf8, embedded labels, table is @1);
Left Join
Load Distinct Year1 as Year
Resident T1;
T2:
CrossTable(Year2,Qty2,2)
LOAD
Cat2,
"Year",
Y20,
Y21,
Y22,
Y23,
Y24,
Y25,
Y26,
Y27,
Y28,
Y29
FROM [lib://Qlik]
(html, utf8, embedded labels, table is @2);
Left join(T1)
Load Distinct Cat2,
year(Date#("Year",'YYYY')) as Year,
year(Date#(right(Year2,2),'YY')) as Year1,
Qty2
Resident T2;
Drop Tables T2;
// You need to include all category field in final load and group by statement as well
T3:
Load Cat2,
Year,
Cat3,
Cat4,
sum(if(Year1=Year,Qty)) as Qty,
sum(Qty*Qty2) as AggrQty
Resident T1
Group by Cat2,Cat3,Cat4,Year;
Drop Table T1;
Much Appreciated Kush..
Will try the solution tomorrow and report back.
if that's a problem you can share a sample data with the expected values to analyze
Thank you Toufiq and Kush....Both of the solution works.... In the end I make a blend of your solutions.....It works for many more dimensions (Cat, Cat3........ Cat14) as well.