Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
See why BI users voted Qlik #1 in 11 categories. GET REPORT
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

weighted aggregation in script

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:

Cat2YearQty
AA202016,0
AA202111,0
AA202221,0
AA202324,0
AA202422,0
AA20255,0
AA20261,0
AA20272,0
AA202818,0
AA202924,0
AB202023,0
AB202118,0
AB202215,0
AB202312,0
AB202412,0
AB202510,0
AB202611,0
AB202714,0
AB20283,0
AB202913,0

 

Table2:

Cat2YearY20Y21Y22Y23Y24Y25Y26Y27Y28Y29
AA20209,000,000,000,000,000,000,000,000,000,00
AA20214,008,000,000,000,000,000,000,000,000,00
AA20224,004,007,000,000,000,000,000,000,000,00
AA20238,002,002,003,000,000,000,000,000,000,00
AA20241,007,009,004,004,000,000,000,000,000,00
AA20253,007,003,005,007,002,000,000,000,000,00
AA20267,009,004,004,002,001,003,000,000,000,00
AA20272,009,007,003,004,005,007,007,000,000,00
AA20289,002,002,005,005,005,004,008,000,000,00
AA20294,004,005,005,006,005,006,009,005,000,00
AB20204,000,000,000,000,000,000,000,000,000,00
AB20218,001,000,000,000,000,000,000,000,000,00
AB20222,009,005,000,000,000,000,000,000,000,00
AB20235,009,007,008,000,000,000,000,000,000,00
AB20249,005,001,006,006,000,000,000,000,000,00
AB20257,006,009,004,007,006,000,000,000,000,00
AB20262,007,005,001,006,006,005,000,000,000,00
AB20271,004,004,003,006,004,004,006,000,000,00
AB20281,003,008,002,009,004,003,004,000,000,00
AB20298,006,005,005,003,003,002,001,009,000,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

 

 

Labels (4)
2 Solutions

Accepted Solutions
Highlighted
Master
Master

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 :

Capture.PNG

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

Highlighted
MVP
MVP

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

7 Replies
Highlighted
Master
Master

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 :

Capture.PNG

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

Highlighted
MVP
MVP

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;
Highlighted
Contributor II
Contributor II

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? 

Highlighted
MVP
MVP

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

Highlighted
Contributor II
Contributor II

Much Appreciated  Kush..

Will try the solution tomorrow and report back.

 

Master
Master

@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") 😉
Highlighted
Contributor II
Contributor II

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.