Hi Guys,
Please take a look a the code below.
I am retrieving data from external qvd file, then creating some new field (based on data from same qvd) and joining to the first table - Table1.
Since the fields I am adding will dynamically change, I woul like to do it (the join) with a loop.
The issue is that doing the left join once as in the commented script below takes about 2 seconds, while doing the same with the for next loop takes several minutes...). Everything is the same, except the loop.
Is there any good advice to work it around?
Thanks in advance,
Aldo.
Table1:
LOAD Distinct
*
FROM $(vTableDir)$(vTable).qvd (qvd);
//Loop over each currency and create currency translated coulumn
FOR EACH vCurr IN $(vCurrToRatesCross)
LET vCurrCode =Null();
CurrCode:
LOAD Distinct
Upper(CODE) AS CODE
FROM $(vAssociated)CURRENCIES.qvd (qvd)
WHERE CURRENCY =$(vCurr);
LET vCurrCode =Peek('CODE',-1,'CurrCode');
LET vField =Null();
////// It takes too much time... WHY ?? In the meanwhile, use as following:
// LEFT JOIN (Table1)
// LOAD Distinct
// If( OrdI.CURRENCY =$(vCurr), $(vField1), $(vField1) *OrdI.IEXCHANGE / If( OrdI.Rate_$(vCurrCode) <>0, OrdI.Rate_$(vCurrCode), 1) ) AS $(vField1)_$(vCurrCode)
// ,If( OrdI.CURRENCY =$(vCurr), $(vField2), $(vField2) *OrdI.IEXCHANGE / If( OrdI.Rate_$(vCurrCode) <>0, OrdI.Rate_$(vCurrCode), 1) ) AS $(vField2)_$(vCurrCode)
// ,If( OrdI.CURRENCY =$(vCurr), $(vField3), $(vField3) *OrdI.IEXCHANGE / If( OrdI.Rate_$(vCurrCode) <>0, OrdI.Rate_$(vCurrCode), 1) ) AS $(vField3)_$(vCurrCode)
// ,If( OrdI.CURRENCY =$(vCurr), $(vField4), $(vField4) *OrdI.IEXCHANGE / If( OrdI.Rate_$(vCurrCode) <>0, OrdI.Rate_$(vCurrCode), 1) ) AS $(vField4)_$(vCurrCode)
// ,_ConvertToCurrencyKey1
// FROM $(vTableDir)$(vTable).qvd (qvd);
FOR EACH vField IN $(vSpreadFieldList)
LEFT JOIN (Table1)
LOAD Distinct
If( OrdI.CURRENCY =$(vCurr), $(vField), $(vField) *OrdI.IEXCHANGE / If( OrdI.Rate_$(vCurrCode) <>0, OrdI.Rate_$(vCurrCode), 1) ) AS $(vField)_$(vCurrCode)
,_ConvertToCurrencyKey1
FROM $(vTableDir)$(vTable).qvd (qvd);
NEXT
DROP TABLE CurrCode;
NEXT
Exit Script;
