Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
IMPORTANT: Upcoming LEF Database Maintenance, Oct. 3rd - SEE DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Master
Master

How can i have monthly different exchange rate ?

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

1 Solution

Accepted Solutions
Highlighted
Master
Master

Maintain one excel file with Month and Region with Rates.

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

HirisH
“Aspire to Inspire before we Expire!”

View solution in original post

4 Replies
Highlighted
Master
Master

Maintain one excel file with Month and Region with Rates.

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

HirisH
“Aspire to Inspire before we Expire!”

View solution in original post

Highlighted
Master
Master

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

 

Highlighted
Master
Master

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

 

Highlighted
Master
Master

Cool.

HirisH
“Aspire to Inspire before we Expire!”