topic Re: Join issue between two tables in App Development

Join issue between two tables

Gabriel_Foret — Tue, 10 May 2022 14:14:34 GMT

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

Re: Join issue between two tables

Or — Tue, 10 May 2022 14:26:56 GMT

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

Re: Join issue between two tables

Gabriel_Foret — Tue, 10 May 2022 14:38:16 GMT

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

Re: Join issue between two tables

Or — Tue, 10 May 2022 14:44:22 GMT

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.

Re: Join issue between two tables

Gabriel_Foret — Tue, 10 May 2022 14:54:21 GMT

I'll check the post you shared.

Thanks a lot for your help ! 🙂

Re: Join issue between two tables

Gabriel_Foret — Tue, 10 May 2022 16:43:34 GMT

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 !

Re: Join issue between two tables

Or — Wed, 11 May 2022 07:07:03 GMT

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.

Re: Join issue between two tables

Or — Wed, 11 May 2022 07:40:13 GMT

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;

Re: Join issue between two tables

Gabriel_Foret — Wed, 11 May 2022 07:57:13 GMT

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 !