Trouble using variable in the FOR EACH statement rather than a literal

[#1]

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

[Miguel_Angel_Baeyens]

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

[#3]

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

[Miguel_Angel_Baeyens]

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.

[#5]

Hi Miguel

I have corrected this, but it appears that the whole string - i.e. all the currencies are being passed in as one value as shown below?

[Miguel_Angel_Baeyens · Accepted solution]

Hello,

I missed the variable expansion in the for each line:

FOR EACH vConvertCurrency IN $(vCurrencyCodes)

Hope that works.

[#7]

That is it! Thank you so much for your assistance