Joining tables on 2 criteria

[BjoernH]

Hey guys,

I have two tables with the following fields:

Budget:

Date

Product

TotalBudgetbyProduct

Percentages:

Year

Product

Location

PercentagebyProductLocation

My goal is to have a new table, which includes Date, Product, Location and NewField 'Budget' (TotalBudgetbyProduct *PercentagebyProductLocation). Therefore, the last field must be calculated, if following criteria is true:

Year(Budget.Date) = Percentage.Year

&

Budget.Product = Percentage.Product

Any help is highly appreciated! 😀 Looking forward to read your comments

[RsQK]

Hey, try this:

Budget:
LOAD *,
AUTONUMBER(BudgetYear & '|' & BudgetProduct) AS Percentage.#key;

LOAD
Date(Date) as Date,
Year(Date) as BudgetYear,
Product AS BudgetProduct,
TotalBudgetByProduct;

LOAD * INLINE
[
Date,Product,TotalBudgetByProduct
01.01.2019,A,10000
01.01.2019,B,12000
01.01.2020,A,15000
01.01.2020,B,7000
];

Percentages:
LOAD *,
AUTONUMBER(Year & '|' & Product) as Percentage.#key;

LOAD * INLINE [
Year,Product,Location,PercentagebyProductLocation
2020,A,Location 1,0.6
2020,A,Location 2,0.8
2020,B,Location 1,0.5
2020,B,Location 3,0.99
2019,A,Location 1,0.45
2019,A,Location 2,0.89
2019,B,Location 1,1
2019,B,Location 3,0.75];

[BjoernH]

@RsQK : Man Thx for your solution!

In the meantime I found another solution whick works absolute fine for me 🙂

//SALES BUDGET VALUES

Sales_Budget_Values:

Crosstable(ProductCode, TotalBudget, 2)

Select Date, Year(Date) as 'Year', A, B, C FROM Table1;

//SALES PERCENTAGE VALUES

Sales_Perc_Values:

CrossTable(ProductCode, Percentage, 2)

SELECT Year, Location, 1, 2, 3 FROM Table2;

TableTemp:

Load
Date,
Year,
ProductCode,
TotalBudget
Resident Sales_Budget_Values;

Left Join(TableTemp)

Load
Year,
Location,
ProductCode,
Percentage
Resident Sales_Perc_Values;

Sales_Budget:
Load
Date,
ProductCode,
Location,
TotalBudget * Percentage as Budget
Resident TableTemp;

Drop Table Sales_Budget_Values;
Drop Table Sales_Perc_Values;
Drop Table TableTemp