Difference Brands with Multiple product sizes

[ShannaJJ27]

Hi There,

I  am working on a project in Qlik Sense and I desperately need help.

I have a 3 Tables in Excel 

Table 1: is pricing for FinYear 2016

Brand , 1kg , 2kg, 500kg, 1000kg

Brand1, 50, 100, 200, 500

Brand2, 60, 50, 600, 800

Table2: is pricing for FinYear 2017

Brand , 1kg , 2kg, 500kg, 1000kg

Brand1, 60, 200, 400, 600

Brand2, 60, 80, 700, 900

Table3 : Sales Quantities

Brand, Qty, PackSize, Date

Brand1, 500, 1kg, Aug-2015

Brand2,400, 2kg, Apr-2015...

Also the Financial Year runs Jul-Jun 

How do I associate the tables to get the sales revenue for each financial year using the right price for the right brand and the right Pack Size?

[Lisa_P]

Try this script:
FinYear2016:
CrossTable(PackSize, Qty)
Load * Inline [
Brand , 1kg , 2kg, 500kg, 1000kg
Brand1, 60, 200, 400, 600
Brand2, 60, 80, 700, 900
];

Pricing:
Load * , '2016' as FinYear
Resident FinYear2016;

Drop table FinYear2016;

FinYear2017:
CrossTable(PackSize, Qty)
Load * Inline [
Brand , 1kg , 2kg, 500kg, 1000kg
Brand1, 60, 200, 400, 600
Brand2, 60, 80, 700, 900
];

Load *, '2017' as FinYear
Resident FinYear2017;

Drop Table FinYear2017;

SalesQuantitiesTemp:
Load * Inline [
Brand, Qty, PackSize, Date
Brand1, 500, 1kg, Aug-2015
Brand2,400, 2kg, Apr-2015
];

Join (Pricing)
Load *,
If(Month='Jan' or Month='Feb' or Month ='Mar' or Month='Apr' or Month = 'May' or Month = 'June', Year-1, Year) as FinYear;
Load *,
SubField(Date, '-', 1) as Month,
SubField(Date, '-', 2) as Year
Resident SalesQuantitiesTemp;

Drop table SalesQuantitiesTemp;
Drop fields Month, Year;