Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
hi
Please Use RIGHT JOIN instead of using Left join
regards
Tahemas
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
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
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 ?