Why loop a table is so slow?

[fortaleza40]

Hello Comunity,

I am looping a table of 217 columns, i only loop one row and all the columns to get the column name and value, but this is taking a lot of time and i can not understand why?, this increase the time of a task from 10 minutes to 1 hour, any idea why or what i can do an alternative to for?, i need to loop to check if the column is date or not:

//Count columns in table

LET NumCols=NoOfFields('Table_Test');

//Variable to set one row loop

LET vOneRow = 1;

//Loop the first row

FOR i=1 to $(vOneRow)

  //Loop all the columns

  FOR j=1 to $(NumCols)

            LET vFieldName = FieldName(j,'Table_Test');

            LET vFieldValue = FieldValue(vFieldName,$(i)); 

            //If it is a date

           If Len(Num(vFieldValue))=5 Then

                 // Do something  

           End if

   NEXT;

NEXT;

[andoryuu]

Looping is going to be slower than set based analysis. It's the nature of the processing engine. If you can fill in your code in your "Do something" block perhaps you can be helped to write your code in a set based way that will do this faster.

[fortaleza40]

I test the other part of the algorithm and is very fast, the slow part is the double FOR, it is very slow, if i use a LOAD is very fast but saddly i need to detect if the field is date or not so i can not LOAD using specific field name, i have to check every field.

Andoryuu, there is another way to loop a table in memory without using FOR?

[fortaleza40]

Which sentence is more fast in Qlik?:

For Each

For

Do While

[marcus_sommer · Accepted solution]

I would say the loop you showed will be quite fast and if takes some time (more as expected) it's your "Do something" part - which are assumingly some load-statements on your large table and that is than not very surprising.

I just took your approach with some small adjustments (which are not essential from a performance point of view):

FOR j=1 to nooffields('Filvks')
xxx:
    load fieldname($(j), 'Filvks') as FieldName,
    fieldvalue(fieldname($(j), 'Filvks'), 1) as Fieldvalue,
    (len(num(fieldvalue(fieldname($(j), 'Filvks'), 1)))=5) as CheckDate,
    now() as Timestamp
autogenerate 1;
NEXT

by a table with 112 columns and nearly 8M of records and it needed just 4 seconds to create this check-table. Even with your 217 columns and maybe much more of records I would expect to get a result in less than a minute.

I could imagine that this xxx-table could be also used to apply your adjustments and this will perform as to do it within the loop. Further it looked quite likely that your table is a crosstable and if it's often useful to transform it into a stream-table.

Beside this - by my data is the check just of a 5 digit numeric field not valide to define if it's a date or not.

Update: another hint: the fieldvalue() fetched the first value from the system-table and not the the value from the first row from your specified data-table.

- Marcus

[fortaleza40]

Marcus,

With the LOAD the improve of the speed was very good, i think LET variables is very slow compared with the LOAD solution, the same FOR using LOAD is more fast.

I needed that table to apply a special date format to some columns.

"Beside this - by my data is the check just of a 5 digit numeric field not valide to define if it's a date or not."

That was the way i found to detect dates in the forums, if you have another way better please share it, i have the problem i am getting the zip codes too as the length is the same, 5.

https://community.qlik.com/t5/QlikView-App-Development/Easy-way-to-check-if-a-field-is-a-Date-or-Str...

"Update: another hint: the fieldvalue() fetched the first value from the system-table and not the the value from the first row from your specified data-table."

Thank you, i didn't know that, the documentation about fieldvalue() didn't specify this.

[marcus_sommer]

Without any further information (maybe from the fieldname, any meta-data, formattings and so on - from the queried field and maybe also from the previous or the following ones - and/or not only querying a single values else all values of the field) it's not possible to detect if it's a date or not. The reason is quite simple - the values of other numeric fields may overlap with the dates.

Of course the above hinted possibilities will need some efforts to develop a stable logic and also in the run-time - but it's possible. I assume that there is no formatting or meta-data available and also the fieldnames are not very reliable but I think the range of your dates are quite restricted - maybe the last 3 - 5 years. This means the numeric values must be >= today() - (5 * 365) <= today() and also the max. number of distinct fieldvalues could be 5 * 365 - in your case it might be a bit differ but I think the main-logic is clear that it could be checked over all fieldvalues.

To check this you could include a check to fieldvaluecount() within your loop and skip all inappropriate fields and the remaining ones are checked with a min/max-load like here descriped: https://qlikviewcookbook.com/2013/09/fastest-method-to-read-maxfield-from-a-qvd/ .

- Marcus