topic Re: Problem with left join in QlikView

Problem with left join

geogou1973 — Thu, 28 Sep 2017 08:59:17 GMT

Hello,

I have problem with the data that i take from 2 tables when i left join them

table1:

LOAD ITEM_ID,

QTY,

STORE_ID

FROM SALES_PER_QTY.QVD (qvd);

left join (table1)

table2:

LOAD ITEM_ID,

STORE_ID,

SALES_DATE,

SALES_QTY

FROM SALES_2017.QVD (qvd);

in the first table i have collected the summary of qty per store and item of the last 30 days and in the second table i have

the sales per date, store, item and qty.

I want to have all the items per store from the first table and the fields SALES_DATE and SALES_QTY from the second and where there is no sale_qty from the second table to have null values in SALES_DATE and in SALES_QTY.

With the script above i have only the common data and i don't know why.

I have think also the idea of applymap but i haven't find the correct code to do it.

Thank you in advance.

Re: Problem with left join

Anonymous — Thu, 28 Sep 2017 09:04:18 GMT

Have you tried joining the summary table onto the Sales information?

I think your just joining the wrong way around. So either try change your statement to a right join.

Or load table 2 first, and join table 1 onto it.

Re: Problem with left join

Anonymous — Thu, 28 Sep 2017 09:09:55 GMT

Please Use RIGHT JOIN instead of using Left join

regards

Tahemas

Re: Problem with left join

geogou1973 — Thu, 28 Sep 2017 09:14:23 GMT

Hello ,

I have tried this but i take all the data from the table that i have the sales per date and i want all the items per store from the first table to join with the items of the second and for the unmatching items to give me null values

Re: Problem with left join

effinty2112 — Thu, 28 Sep 2017 09:54:46 GMT

Hi,

What information is in SALES_PER_QTY.QVD that is not in SALES_2017.QVD?

Would this load from SALES_2017.QVD give table1?

table1:

LOAD

ITEM_ID,

STORE_ID,

sum(SALES_QTY) as QTY

WHERE SALES_DATE > today()-30

GROUP BY ITEM_ID,STORE_ID;

LOAD ITEM_ID,

STORE_ID,

SALES_DATE,

SALES_QTY

FROM SALES_2017.QVD (qvd);

If all the information in SALES_PER_QTY.QVD can be found in SALES_2017.QVD then just load from SALES_2017.QVD and forget joining.

Regards

Andrew

Re: Problem with left join

geogou1973 — Thu, 28 Sep 2017 10:17:01 GMT

Hello,

from the first table i have the qty of the last 30 days for the items that the qty is bigger than 100 or 200 or 300 pieces

according to the store. For the second table i will take the the qty for the items of the first table per day. If i don't have any sale i want to have null values in the qty of the second table.

table1 table2

store_id item_id qty sales_date store_id item_id sales_qty

23 123456 301 20/9/2017 23 123456 15

23 456789 450 20/9/2017 23 456789 10

23 789045 564 20/9/2017 23 789045 5

23 345673 353 20/9/2017

Join table

store_id item_id sales_date sales_qty

23 123456 20/9/2017 15

23 456789 20/9/2017 10

23 789045 20/9/2017 5

23 345673 null null

That is the data i want to have after the join.

How can i get it ?