Case 881 How to create expression to compute inventory Amount with Latest exchange Rate ?

[paulyeo11]

Hi All

I have below load script working fine :-

// Part 1 Load the inventory raw data for SOURCE indonesia  

INVENTORY:
LOAD
'TI' as SOURCE,
(@54:60T)*(@61:75T) as C_ONHAND_AMT
From $(vRAWPath)$(vFile263)(ansi, fix, no labels, header is 0, record is line);

// Part 2 Load the inventory raw data for SOURCE Singapore 

LOAD left (FileBaseName(), 4) AS TDSS_invc,
'TS' as SOURCE,
(@54:64T)*(@65:75T) as C_ONHAND_AMT,
Date((@286:296T), 'DD-MMM-YYYY') as INVC_DATA_TDSS
FROM $(vRAWPath)$(vFile253) (ansi, fix, no labels, header is 0, record is line);

// Part 3 Load the monthly exchange table

LOAD SOURCE,
Rate,
MonthName(date) as [SalesMonth], //Join on to sales table -> Dont change name
Rate_
FROM
Map_Rate_.xlsx
(ooxml, embedded labels, table is Sheet);

My Table need expression for compute amount base on latest exchange rate for June 15000.

I have Try :- Sum(C_ONHAND_AMT)*Rate , it display Null.

My question is how to compute 301,887,280 X 15000 ( 2020 June Exchange Rate )

My data model :-

Hope some one can advise me.

Paul Yeo

[Saravanan_Desingh · Accepted solution]

Can u try this?

Sum(C_ONHAND_AMT)*FirstSortedValue(Rate,-SalesMonth)