For Loop taking lot of time

[#1]

Hi All ,

I want to inner join seven similar tables to one main table.

When I  am doing this inside a for Loop then this is taking long time (5 hours) and high RAM usage too , and when I keep them one after another it is getting loaded quickly (about 10 mins).

I am not able to understand this behavior of Qlik. Can someone explain why this is happening or what mistake I have done in code.

PFB the Code Snippet which I am using:

When using For Loop :

LET vL.NoRows = NoOfRows('tablename')-1;

FOR i = 0 TO $(vL.NoRows)

LET vL.Table = Peek('first column', $(i), 'tablename');

LET vL.Desc = Peek('second column', $(i), 'tablename');

inner join(Main_Table)

LOAD

  [$(vL.Table)] as [$(vL.Desc)],

  %UserNAME

From  $(vL.Table) ;

drop Table $(vL.Table);

NEXT

-------------------------------------------

' tablename ' is table which contains all the seven table names and there descriptions .

When Not using For Loop :

inner join(Main_table)

LOAD Distinct

  Column1 as ABCD,

  %UserNAME

RESIDENT Table1;

drop Table Table1;

inner join(Main_table)

LOAD Distinct

  Column2 as EFGH,

  %UserNAME

RESIDENT Table2;

drop Table Table2;

Please help.

[Anonymous]

Similar Tables means?

Why you need joining?

If you have similar tables in terms of Data Structure then use auto Concateneation feature..

[Anonymous]

QV automatically will do it if you are having same table structure

[jonathandienst]

I would expect the two processes to take about the same time, if they were the loading/joining the same thing. But you have a major difference -- the fast example uses resident loads, which are a lot faster (or is that a typo?).

Are you adding one new field for each join in your real app? If that is the case, I would convert your process to using applymap, which should be much faster than a join. Just do a mapping load of all the tables in 'tablename' before your main load, and then use ApplyMap in your main load to add the extra fields.

[marcus_sommer]

Your noticed big difference within the runtime could be caused from too less RAM then qlikview runs only fast if it could process the data within the RAM - is there are need to swap RAM to the virtual RAM on the harddisk it will go very slow.

- Marcus

[raghvendrasingh]

Hi Abhishek,

When you using For loop,it executes the data row by row (So if rows are less then will take less time).

In Without loop , whole table is executing simultaneously.

Hope it will helps.

Regards,

Raghvendra

[#7 · Accepted solution]

Hi All ,

Thanks for your replies , the time difference was because [$(vL.Desc)] variable in loop was giving some other name then 'ABCD' (required column description) , so in place of inner join it was doing a Cartesian product of records , as it was not getting any column with same name in 'Main_table'  .