Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone.
I have to tables like those ones :
I'd like to join these two in order to have for each week, each shop and its sales (0 if there are no sales)
I'm a little bit confused, nothing i've tried seems to be working.
Thanks in advance for your help !
-Gabriel
To join the tables, simply:
Load * From Table1;
Join
Load * From Table2;
You could use Left Join if desired.
Note that this has nothing to do with filling in missing values when a shop has no line for a specific week - that's a whole different can of worms, see: https://community.qlik.com/t5/QlikView-Documents/Generating-Missing-Data-In-QlikView/ta-p/1491394
Actually, thinking of it again, you might be able to achieve this fairly easily with a Cartesian join. Should look something like:
Temp:
Load Distinct Week, 0 as Sales
From Table1;
JOIN
Load Distinct ShopID
from Table2;
Load Week, ShopID, Sales from Table1; // This will go into the Temp table as well
Final:
Noconcatenate Load Week, ShopID, Sum(Sales) as Sales
Resident Temp;
Join
Load * From Table2;
Drop Table Temp;
To join the tables, simply:
Load * From Table1;
Join
Load * From Table2;
You could use Left Join if desired.
Note that this has nothing to do with filling in missing values when a shop has no line for a specific week - that's a whole different can of worms, see: https://community.qlik.com/t5/QlikView-Documents/Generating-Missing-Data-In-QlikView/ta-p/1491394
Thanks for your quick reply !
In fact, I did join them simply like you said :
Sorry to bother you with that, i might be wrong somewhere.
But in my analysis table, i only have the lines for the shops with sales and one for the Shop4 with no sales.
That is the correct behavior - if you don't want the extra line, you can use left join instead of just join.
A join operation will not create lines that don't exist, which is the case for any shops that did not have sales in a specific week. Have a look at the post I previously linked insofar as filling in missing/partial data.
I'll check the post you shared.
Thanks a lot for your help ! 🙂
So, I had a look on the link you shared (thanks for that!)
But in all the examples presented on that pdf, i'm not sure that it applies to my case.
Each time, the join is done for a unique field that could be missing.
Here I have to match one measure with two dimension fields (so it creates missing cells where no sales is stored).
(Our target is for each week to have Shop1, Shop2, Shop3, Shop4).
If you have any advice,
Thanks !
Near as I can tell, your issue has nothing to do with joining. You need to make up missing week/shop data in Table1 so that each shop has a line for each week (with 0 sales if no line existed). A simple join back to Table2 will then work.
Actually, thinking of it again, you might be able to achieve this fairly easily with a Cartesian join. Should look something like:
Temp:
Load Distinct Week, 0 as Sales
From Table1;
JOIN
Load Distinct ShopID
from Table2;
Load Week, ShopID, Sales from Table1; // This will go into the Temp table as well
Final:
Noconcatenate Load Week, ShopID, Sum(Sales) as Sales
Resident Temp;
Join
Load * From Table2;
Drop Table Temp;
Hi again Or !
Just now, I was trying something like that, with a Cartesian join at first.
It works with your help, thanks a lot !