How to faster up left join in for next loop

Fri, 19 Oct 2012 08:31:17 GMT

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;

topic How to faster up left join in for next loop in QlikView

How to faster up left join in for next loop