Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
BjoernH
Contributor II
Contributor II

Joining tables on 2 criteria

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

 

 

Labels (1)
2 Replies
RsQK
Creator II
Creator II

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
Contributor II
Contributor II
Author

@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