How can i have monthly different exchange rate ?

[paulyeo11]

Hi All

I have below load script , work fine , Now the exchange rate =2.4 , may i know how to modify below script so that it can load :-
Jan 2020 = 2.3
Feb 2020 = 2.6

LOAD * INLINE [
SOURCE,Currency, Rate
TDSS,SGD, 1
ADL,SGD, 1
TDSPG,RM, 2.4
TDSKL,RM, 2.4
TDST,THAI, 25
];

sales:
LOAD
Date#(date_inv, 'D/M/YYYY') AS date,
LineSalesAmount,
profit_amt,
inv_no,
BRAND,
company,
SOURCE,
sbu,
ODSE

INLINE [date_inv, LineSalesAmount,profit_amt,inv_no,BRAND,company,sbu,ODSE,SOURCE
14/1/2020, 1,4,1235 ,YASKAWA,JCS,AP_SBU,AP,TDSPG
14/2/2020, 2,4,1235 ,YASKAWA,JCS,AP_SBU,AP,TDSPG
];

I have try below :-

LOAD * INLINE [
SOURCE,Currency, Rate , date
TDSPG,RM, 2.3,14/1/2020
TDSPG,RM, 2.5,14/2/2020
];

it does not work.

Hope some one can advise me

Paul Yeo

[HirisH_V7 · Accepted solution]

Maintain one excel file with Month and Region with Rates.

So that  you can map it with Data driven Month, that will do.

[paulyeo11]

Hi Sir

Thank you for your advise  , i have try to convert inline load to excel file load. I encounter some issue , ope you can advise me.

// Section (A)load change rate table

LOAD * INLINE [
SOURCE,Currency,Rate
TPG,RM,2.4
TDT,THAI,25
TDS,SG,1
ADL,SG,1
];

// Section (B) Load sales data table

sales:
LOAD
Date#(date_inv, 'D/M/YYYY') AS date,
LineSalesAmount,
profit_amt,
inv_no,
BRAND,
company,
SOURCE,
sbu,
ODSE

INLINE [date_inv, LineSalesAmount,profit_amt,inv_no,BRAND,company,sbu,ODSE,SOURCE
14/1/2020, 1,4,1235 ,YASKAWA,JCS,AP_SBU,AP,TDS
14/2/2020, 2,4,1235 ,YASKAWA,JCS,AP_SBU,AP,TPG
14/2/2020, 2,4,1235 ,YASKAWA,JCS,AP_SBU,AP,TDT
14/2/2020, 2,4,1235 ,YASKAWA,JCS,AP_SBU,AP,ADL
];

When i replace Section (A) load script with excel table below :-Directory;
LOAD SOURCE,
Currency,
Rate,
Rate_,
date_t
FROM
Map_Rate.xlsx
(ooxml, embedded labels, table is Sheet1);

The Currency and Rate field from Excel table cannot link with sales Table.
Hope you can advise me where go wrong ?

enclosed excel and QVW

Paul

[paulyeo11]

Hi Sir

it work fine now.

Due to my excel table SOURCE field label and in line load sales table different , after i change it , it work fine

Paul

[HirisH_V7]

Cool.