Get data from two table

[#1]

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

[Sokkorn]

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

[sujeetsingh]

Ratan,

you can easily do it by using joins on common id or field.

[#4]

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;

[er_mohit]

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;