Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
atsushi_saijo
Creator II
Creator II

FOREX Integration on Loading Script

**My sincere apology that initial loading script posted on 13.02.2014 was faulty. Further QA tests, I updated the trial script.

[Fix 1] mapping was missing;

[Fix 2] MonthEnd needs to be further rounded downwards by FLOOR();

[Pending 3] The attached exchange rate misses the latest (of this month). I will do certain update on this script later.

***************************

Dear Experts, I wished to share the FOREX integration example. I would be grateful for your feedback.

On the loading script, we have externally-obtained exchange rate (http://www.oanda.com/currency/historical-rates/). In my case, data were fetched as attached.

INITIAL LOAD

FOREX data are loaded first.

TMP:

LOAD DATE([END DATE],'YYYY-MM-DD') AS F.DT,

     [EUR/USD] AS USD,

     [EUR/GBP] AS GBP,

     [EUR/JPY] AS JPY,

     [EUR/CZK] AS CZK

FROM

$(PATH)data.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

TABLE CONVERSION

Further, the loaded tables are converted into single table, ready for mapping.

FOREX: LOAD F.DT,USD RESIDENT TMP;

CONCATENATE(FOREX) LOAD F.DT,GBP RESIDENT TMP;

CONCATENATE(FOREX) LOAD F.DT,JPY RESIDENT TMP;

CONCATENATE(FOREX) LOAD F.DT,CZK RESIDENT TMP;

FX: MAPPING LOAD * RESIDENT FOREX;

MINMAX: LOAD MIN(F.DT) AS V RESIDENT TMP;

SET VMIN = PEEK('V', 0, 'MINMAX');

LIMITATION ON AVAILABLE TIME SERIES

The maximum data are 5 years, therefore;

MINMAX: LOAD MIN(F.DT) AS V RESIDENT FOREX;

SET VMIN = PEEK('V', 0, 'MINMAX');

ON MAIN LOADING SCRIPT

On the loading script, we create such:

[target value]/APPLYMAP('FX',

  TRIM(B1CD28)&IF(

  FLOOR(NUM(MONTHEND(DATE#(NUM([Target Date]),'YYYYMMDD'))))<$(VMIN)

  ,$(VMIN)

  ,FLOOR(NUM(MONTHEND(DATE#(NUM([Target Date]),'YYYYMMDD'))))

  )

  ,1) AS Exchanged_Figure

CONCLUSIVE REMARK

Initial load would be bulky if target currencies are @150. I wonder there might be a better scripting on this area. I appreciate if you possibly advise any improvements/comments/feedback on the above procedure to convert FOREX.

0 Replies