Load with one Key from Tables that were joined

[Karineyumi]

Hi everyone, please help meeee.

I have tree tables one called "Saleskit", the other "Monthstable" and the "Database":

Which basically, I have a whole database for budgets segmented by customer/sales rep/year, and i need to show those numbers by year/month/week, with that, I joined the "Saleskit" with "Monthstable" to create lines for me by Month/week for each budget. 

And finally I need to create a key from the joined "Saleskit" + "Monthstable" table into my "Database" :

"Lara code"&"Export / Import/ FF"&Year1&Month1&Week1 as SaleskitKey

SALESKIT:
LOAD
Country as "Sales Country",
"Sales Manager",
"Sales rep",
"Sales Role",
"Lara BP code" as "Lara code",
"Customer name",
"Export / Import/ FF",
"Vol. Budget (Teus)"/52 as "Vol. Budget (Teus)",
Segmentation,
"Year1"
FROM [lib://QS_Projects_BA_Miami_RO/30_External source files/SALESKITDATABASE.qvd]
(qvd);

join
Monthstable:
LOAD
Year1,
Month1,
Week1
FROM [lib://AttachedFiles/Monthstable.xlsx]
(ooxml, embedded labels, table is Monthstable);

SALESKITMONTH:

Load

"Sales Country",

    "Sales Manager",

    "Sales rep",

    "Sales Role",

    "Master Customer Name",

    "Customer name",

    "Vol. Budget (Teus)",

    "VAS Budget ($)",

    "CH Budget (Teus)",

    Segmentation,

    Category,

    "Master BP code",

    "Country_Code",

"Lara code"&"Export / Import/ FF"&Year1&Month1&Week1 as SaleskitKey

Resident SALESKIT;

drop Table SALESKIT;

The problem that I`m having is that not all the customers in the Saleskit will be in the Database and vice versa. But I need to have both information, so its showing a lower Budget from Saleskit then it should. 

**I cannot do the Month/week inclusion in the excel (which is were my data comes from) because i have around 700.000 lines on Saleskit table and I have a 3 year worth of data to have month/weeks numbers 

thank youuuuu appreciate your help. 

[PrashantSangle]

Can you explain your query with sample data & expected output.

[Karineyumi]

hi PrashantSangle,

sure thing.

For the Saleskitable and the Monthstable joined i need this output:

Saleskit table:

Monthstable:

OUTPUT:

and then with this Key (column in red) i will connect with my Database:

So for the final Output, I need an Outer Join. Information from all tables. 

Because I need to do the Budget vs the Volume by year/month/week but i also need the information on customers that are on table Saleskit but not on Database and customers that are on Databse but not on Saleskit.

thank you for you time 🙂