Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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
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?
Which sentence is more fast in Qlik?:
For Each
For
Do While
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
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.
"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.
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