Filter by column and average Aggr

[A_Tan]

Hi Qlik Community, i need some help here.

Sheet A:

Sheet B:

In layman terms, i want to:

If B.Product = A.Product, Average ( Aggr ( (Hours /  Quantity), B.Product))

Result = 3.74

If B.product is contain in A.Product, find the average of aggr of (hour / quantity) by B.product.

Ie. Product A, C, D, F

Thanks, Alvin

[MayilVahanan]

Hi

Try like below

LOAD *, 1 as FlagA Inline
[
Product, Efficiency
A, 3.4
C, 5.1
D,4.1
F,2.2
];

Load * Inline
[
Product, Hours, Quantity
A, 31, 10
A, 33, 9
B, 51, 10
C, 21, 4
C, 22, 4
D, 8, 2
E, 71, 5
E, 75, 6
F, 66, 30
F, 63, 28
];

o/p:

Exp: Avg ({<FlagA = {1}>} Aggr ( Sum({<FlagA={1}>}Hours) / Sum({<FlagA={1}>}Quantity), Product))