In For Each Loop 2nd, 3rd etc., Iterations are giving incorrect output.

[udayk86081]

Hello Everyone

Hope I will get a quick resolution to my below QlikSense query

In "For Each Loop" 2nd, 3rd etc., Iterations are giving incorrect output and only 1st Iteration is giving Correct Output.

I'm using For Each Loop to loop through a number of files in a Folder.

Example script:

For each File in Filelist ('lib://*********')

Table1:

Load

*

From [$(File)]

Table 2:

Mapping Load

filed 1,

filed 2

Resident Table 1 group by field 1;

Table 3:

Field 1,

Filed 2,

filed 3,

From [$(File)]

Table 4:

Mapping Load

Filed 1,

Filed 2

resident Table 3 order by Field 2 DESC;

Table 5:

Field 1,

Field 2,

Filed 3, 

and etc.,

From [$(File)]

Drop Tables Table 1, Table 3;

Next File

For some calculations/transformations, I want the For loop to run through all the above tables and Table 5 is my Final Output table.

Thanks in Advance

[marcus_sommer]

Chaining multiple statements within loops could be tricky and have the potential to make simple things complicated. Therefore I would suggest to simplify the logic maybe by loading at first the raw-data per loop and applying the wanted transformation afterwards.

Beside of this general recommendation I think that your mapping didn't work because mapping-tables will be automatically removed by finishing the script - until then they exists and within the next iteration the table-names of the mapping loads will be also iterated.

Means in the first run you start with [Table 2] and the next iteration will create a table [Table 2-1] and so on. And of course all following applymap() calls won't relate to right mapping-table - and because of the fact that the origin mapping-table exists no error will occur.

A way to bypass this challenge is to control the table-names, for example by applying the iteration-counter/information to the table-name. In your case maybe with something:

...
let vTable = subfield('$(File)', '\', -1);
...
[Table 2 - $(vTable)]: mapping load ....
...

and the same of course within the applymap().

[udayk86081]

Hello Marcus

Thanks a lot for your detailed explanation.

I simplified the logic and instead of Mapping function I used LookUp function and its giving correct results now.
But the  application reload time has increased from 20 Seconds to 60 seconds. I'm ok with the reload time.

But If you have any other function instead of lookup function that can reduce the reload time , please suggest.

Thank You.

[marcus_sommer]

Nothing is so fast, flexible and powerful as mappings especially if the requirements get more complex and/or there are bigger datasets. Lookup() is applicable if the datasets have an Excel-sizing but not much more.