Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am loading monthly sales volume by product from an OLE DB connection and then I have my gross margin by product in a csv file. I want to be able to multiply the gross margin for a product by its respective volume to create graphs and KPI's. Is there a way I can merge the queries in the data load editor?
I already tried just doing a calculated measure in the app overview and it isn't returning anything.
Here is what my script looks like right now (minus proprietary details )
//CSV Gross Margin Load
LOAD
Product as "Product Num",
GM_Per_Lb
FROM [lib://Gross Margin/Gross Margin.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
//Sales History Load
LIB CONNECT TO '****************************************';
SQL SELECT Territory,
"Bill To Name",
"Ship To Name",
"Ship Date",
"Product Num",
"Product Desc",
"Actual Fill Weight",
FROM "DC59_Live".dbo."ADGV_SO_Territory"
LEFT OUTER JOIN "DC59_Live".dbo."en_prod_tbl"
ON "Product Num" = "en_prod_key";
Hi Anderson,
Use Applymap() for this scenario.(Use Product number as a key). Avoid joining , use appymap() it may increase the performance.
Cheers,
Ganesh.
Hi,
Your measure calculated in the application should return a value, we should look for what is wrong here.
Anyway you can try this script :
MAP1:
MAPPING LOAD
Product as "Product Num",
GM_Per_Lb
FROM [lib://Gross Margin/Gross Margin.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
LIB CONNECT TO '****************************************';
MyTable:
LOAD
*,
ApplyMap ('MAP1', GM_Per_Lb) AS GM_Per_Lb;
SQL SELECT Territory,
"Bill To Name",
"Ship To Name",
"Ship Date",
"Product Num",
"Product Desc",
"Actual Fill Weight",
FROM "DC59_Live".dbo."ADGV_SO_Territory"
LEFT OUTER JOIN "DC59_Live".dbo."en_prod_tbl"
ON "Product Num" = "en_prod_key";
This is looking very promising. When I include the code you provided Xavier_CH199 and run the debugger it gets to the "SQL SELECT Territory" and then throws the error "Field not found - <GM_Per_Lb>" do I need to do anything inside the SQL SELECT statement?
Okay I just got it, I had to put ApplyMap ('MAP1', "Product Num") AS GM_Per_Lb; instead. Thanks Xavier and Ganesh for your help! I wouldn't have known to use ApplyMap without you.
Thanks!