Problem with left join

[geogou1973]

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. 

[Anonymous]

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.

[Anonymous]

hi

Please Use RIGHT JOIN instead of using Left join

regards

Tahemas

[geogou1973]

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

[effinty2112]

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

[geogou1973]

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 ?