Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Highlighted
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