Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I have two table as following:
//Stock Balance Table
Directory;
Stock_Balance:
LOAD ValA,
Material,
[From Date],
[Opening Value],
[Closing Value],
(([Opening Value]+ [Closing Value])/2) as _average
FROM Data_Source\*_PPCement_StockBal.xlsx (ooxml, embedded labels, table is Sheet1);
// Cost of Goods Sold Table
Directory;
Cost_of_Goods_Sold:
LOAD [Year/month],
[Profit Ctr],
Material,
[Amount in local cur.]
FROM Data_Source\GL\*_PPCement_GL.xlsx (ooxml, embedded labels, table is Sheet1);
I want to calculate [Amount in local cur.]/_average
how to do it?
Best regards,
Ratana
Hi Ratana,
Not sure this script is working for you or not
//Stock Balance Table
Directory;
Stock_Balance:
LOAD ValA,
Material,
[From Date],
Material &'/'& Text(Year([From Date]) &'/'& Month([From Date])) AS [_Key],
[Opening Value],
[Closing Value],
(([Opening Value]+ [Closing Value])/2) as _average
FROM Data_Source\*_PPCement_StockBal.xlsx (ooxml, embedded labels, table is Sheet1);
[AVG]:
Mapping Load
[_Key],[_average]
Resident Stock_Balance;
// Cost of Goods Sold Table
Directory;
Cost_of_Goods_Sold:
LOAD [Year/month],
[Profit Ctr],
Material,
[Amount in local cur.],
ApplyMap('AVG',Material &'/'&[Year/month],1) AS [Avg],
[Amount in local cur.]/ApplyMap('AVG',Material &'/'&[Year/month],1) AS [YourValue]
FROM Data_Source\GL\*_PPCement_GL.xlsx (ooxml, embedded labels, table is Sheet1);
Assumption: [Year/month] have value in format 2013/05
Regards,
Sokkorn
Ratan,
you can easily do it by using joins on common id or field.
try this
Stock_Balance:
LOAD ValA,
Material,
[From Date],
[Opening Value],
[Closing Value],
(([Opening Value]+ [Closing Value])/2) as _average
FROM Data_Source\*_PPCement_StockBal.xlsx (ooxml, embedded labels, table is Sheet1);
join
LOAD [Year/month],
[Profit Ctr],
Material,
[Amount in local cur.]
FROM Data_Source\GL\*_PPCement_GL.xlsx (ooxml, embedded labels, table is Sheet1);
LOAD *, [Amount in local cur.]/_average as Result
Resident Stock_Balance;
Directory;
Stock_Balance:
LOAD ValA,
Material,
[From Date],
[Opening Value],
[Closing Value],
rowno() as Count,
(([Opening Value]+ [Closing Value])/2) as _average
FROM Data_Source\*_PPCement_StockBal.xlsx (ooxml, embedded labels, table is Sheet1);
join
Directory;
Cost_of_Goods_Sold:
LOAD [Year/month],
[Profit Ctr],
Material,
rowno() as Count,
[Amount in local cur.]
FROM Data_Source\GL\*_PPCement_GL.xlsx (ooxml, embedded labels, table is Sheet1);
New_Stock_Balance :
LOAD *, [Amount in local cur.]/_average as Result
Resident Stock_Balance;
drop table Stock_Balance;