Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Not applicable

How to faster up left join in for next loop

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;