Discussion Board for collaboration related to Creating Analytics for QlikView.
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
LOAD Replace(@1,'.','/') as Date,
'$(vConvertCurrency)' as Currency,
@2 as Rate
(html, codepage is 1252, no labels, table is @1);
Go to Solution.
I missed the variable expansion in the for each line:
FOR EACH vConvertCurrency IN $(vCurrencyCodes)
Hope that works.
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?
LOAD DISTINCT CurrencyCode AS CurrencyCodeTemp
FROM DWPurchaseOrders.qvd (qvd)
WHERE len(CurrencyCode) = '3';
LOAD CONCAT(CurrencyCodeTemp, chr(39) & ',' & chr(39)) AS CurrencyCode
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
$(vCurrencyCodes) as Currency,
I think you should change
$(vConvertCurrency) as Currency,
as vConvertCurrency is the variable that contains for each loop just one of the several elements in vCurrencyCodes.
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?
That is it! Thank you so much for your assistance