Join issue between two tables

[Gabriel_Foret]

Hi everyone.

I have to tables like those ones :

Table1

Table2

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)

Result Table

I'm a little bit confused, nothing i've tried seems to be working.

Thanks in advance for your help !

-Gabriel

[Or · Accepted solution]

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

[Gabriel_Foret]

Thanks for your quick reply !

In fact, I did join them simply like you said :

Script QLIK

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.

Table Result in QLIK

 

[Or]

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.

[Gabriel_Foret]

I'll check the post you shared.

Thanks a lot for your help ! 🙂

[Gabriel_Foret]

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 !

[Or]

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. 

[Or · Accepted solution]

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;

[Gabriel_Foret]

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 !