Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

atsushi_saijo
Contributor 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.

Tags (3)
Community Browser