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

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

Wed, 25 Aug 2010 08:44:03 GMT

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);

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

Miguel_Angel_Baeyens — Wed, 25 Aug 2010 08:54:41 GMT

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

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

Thu, 26 Aug 2010 02:36:11 GMT

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

(html, codepage is 1252, no labels, table is @1);

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

Miguel_Angel_Baeyens — Thu, 26 Aug 2010 07:14:23 GMT

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.

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

Mon, 30 Aug 2010 04:21:26 GMT

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?

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

Miguel_Angel_Baeyens — Mon, 30 Aug 2010 09:00:12 GMT

Hello,

I missed the variable expansion in the for each line:

FOR EACH vConvertCurrency IN $(vCurrencyCodes)

Hope that works.

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

Mon, 30 Aug 2010 09:37:48 GMT

That is it! Thank you so much for your assistance