Load with one Key from Tables that were joined

Karineyumi — Thu, 30 Nov 2023 22:51:02 GMT

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.

Re: Load with one Key from Tables that were joined

PrashantSangle — Fri, 01 Dec 2023 07:05:20 GMT

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

Re: Load with one Key from Tables that were joined

Karineyumi — Fri, 01 Dec 2023 20:40:15 GMT

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 🙂

topic Re: Load with one Key from Tables that were joined in App Development

Load with one Key from Tables that were joined

Re: Load with one Key from Tables that were joined

Re: Load with one Key from Tables that were joined