Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
We would like to substitute the value of the quoted currencies in the FOR EACH statement rather than having a 'closed' list
FOR EACH vConvertCurrency IN 'AUD','USD','EUR' // Want to use variable read in from a table here
RawRate:
LOAD Replace(@1,'.','/') as Date,
'$(vConvertCurrency)' as Currency,
@2 as Rate
FROM
[http://www.oanda.com/currency/historical-rates?date_fmt=normal&date=$(vMaxDate)&date1=$(vMinDate)&exch=$(vBaseCurrency)&exch2=$(vBaseCurrency)&expr=$(vConvertCurrency)&expr2=$(vConvertCurrency)&margin_fixed=0&format=HTML&redirected=1]
(html, codepage is 1252, no labels, table is @1);
NEXT
Hello,
I missed the variable expansion in the for each line:
FOR EACH vConvertCurrency IN $(vCurrencyCodes)
Hope that works.
Hello,
You may read that currency codes from a file or whatever, with something like
CurrencyCodes:LOAD CONCAT(CurrencyCode, chr(39) & ',' & chr(39)) AS CurrencyCodeFROM FILE.QVD (qvd);
LET vCurrencyCodes = chr(39) & Peek('CurrencyCode') & chr(39);
Then use vCurrencyCodes in the For Each statement.
Hope that helps
Thanks for the response. We are still having the problem of the whole string of currencies being passed in at once?
ProntoCurrencyTemp:
LOAD DISTINCT CurrencyCode AS CurrencyCodeTemp
FROM DWPurchaseOrders.qvd (qvd)
WHERE len(CurrencyCode) = '3';
ProntoCurrency:
LOAD CONCAT(CurrencyCodeTemp, chr(39) & ',' & chr(39)) AS CurrencyCode
RESIDENT ProntoCurrencyTemp;
DROP TABLE ProntoCurrencyTemp;
LET vBaseCurrency = 'ZAR';
LET vMinDate = Date(today() - 499,'DD/MM/YY');
LET vMaxDate = Date(today(),'DD/MM/YY') ;
LET vCurrencyCodes = chr(39) & Peek('CurrencyCode') & chr(39);
FOR EACH vConvertCurrency IN vCurrencyCodes
RawRate:
LOAD Replace(@1,'.','/') as Date,
$(vCurrencyCodes) as Currency,
@2 as Rate
FROM
[http://www.oanda.com/currency/historical-rates?date_fmt=normal&date=$(vMaxDate)&date1=$(vMinDate)&exch=$(vBaseCurrency)&exch2=$(vBaseCurrency)&expr=$(vConvertCurrency)&expr2=$(vConvertCurrency)&margin_fixed=0&format=HTML&redirected=1]
(html, codepage is 1252, no labels, table is @1);
NEXT
Hello,
I think you should change
$(vCurrencyCodes) as Currency,
for
$(vConvertCurrency) as Currency,
as vConvertCurrency is the variable that contains for each loop just one of the several elements in vCurrencyCodes.
Hello,
I missed the variable expansion in the for each line:
FOR EACH vConvertCurrency IN $(vCurrencyCodes)
Hope that works.
That is it! Thank you so much for your assistance