Solved: Re: weighted aggregation in script - Qlik Community

shovongoutam · ‎2020-07-15

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

Taoufiq_Zarra · ‎2020-07-15

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 :

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

Kushal_Chawda · ‎2020-07-15

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;

View solution in original post

Taoufiq_Zarra · ‎2020-07-15

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 :

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

Kushal_Chawda · ‎2020-07-15

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;

shovongoutam · ‎2020-07-15

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?

Kushal_Chawda · ‎2020-07-15

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;

shovongoutam · ‎2020-07-15

Much Appreciated Kush..

Will try the solution tomorrow and report back.

Taoufiq_Zarra · ‎2020-07-16

@shovongoutam

if that's a problem you can share a sample data with the expected values to analyze

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

shovongoutam · ‎2020-07-24

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.

weighted aggregation in script

Aggr Sum

Join Table

Multiplication