Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone!
I have a question, because I'm trying to re-adapt a subroutine that I found on QlikFix, which use the script function "AUTONUMBER", but it's only available for versions above 12.4, which I don't have ...
So, it's possible to modify or create a subroutine that for every key on each table with a prefix like "_*" converts it to AutoNumberHash128?
I'm trying something like this:
FOR vT = 0 TO nooftables()-1
LET vTable =tablename(vT);
Let vF = 1; // initialise variable for next loop
Let Nvf = nooffields('$(vTable)');
DO While vF <= Nvf
LET vField=fieldname(vF,'$(vTable)');
IF wildmatch('$(vField)','_Clave*') THEN
Left join($(vTable))
LOAD $(vField), AutoNumberHash128($(vField)) as %_$(vField) RESIDENT $(vTable);
drop field $(vField);
ENDIF
Let vF = vF + 1;
LOOP
NEXT
But it's not working .. do you have any suggestions?
Thanks in advance!
IMO the code-snippet looked quite suitable for your mentioned task. The document-log should give some valuable hints where it breaks. I could imagine that it breaks if you any special chars or spaces within your table/field-names because they need to be wrapped with [] or "" - just add it by each normal field/table-call but not by the reference within the table/field-functions because there the wrapping is ''.
Beside this there is probably not much what could be improved on this approach. The join might be replaced by a mapping but I doubt that it would give a significantly difference. Within the the normal load-logics you couldn't avoid the resident-load because you could do any transforming only against the data-tables and not against the system-tables.
An maybe more performant alternatively may be to use:
which could replace the values from the system-tables. Means in your case to replace the join from the routine with a loop over the field-values and loading it into mapping table.
Personally I wouldn't do something like this because for the most keys I don't need and want an autonumber() and if I would tend to create them within the origin load. Not each key must be an autonumber() especially not if those real-information are also needed which then usually leads to keep the origin field and having an autonumber(). Possible yes, really much benefit, hmmm ...
- Marcus
Hi,
1. It's not true that AutoNumber() did not exist prior to v12.4
2. Can you please post an example of what you have and what you expect?
thanks
Marco
Hi Marco,
1. I've never said autonumber() didn't exists .. what I said is that the script funcion AUTONUMBER '%_*'; (for example to make autonumber all key fields starting with "%_") doesn't exist until 12.4 in QlikView.
2.And what I'm trying to do, is to replicate that behaviour (AUTONOMBER '%_*') on a subroutine, but I don't know which is the best method for that ... because I need to read each table looking for a key field starting with 'a %_' .. which is easy with a loop, and then I have to autonumber() the field.
IMO the code-snippet looked quite suitable for your mentioned task. The document-log should give some valuable hints where it breaks. I could imagine that it breaks if you any special chars or spaces within your table/field-names because they need to be wrapped with [] or "" - just add it by each normal field/table-call but not by the reference within the table/field-functions because there the wrapping is ''.
Beside this there is probably not much what could be improved on this approach. The join might be replaced by a mapping but I doubt that it would give a significantly difference. Within the the normal load-logics you couldn't avoid the resident-load because you could do any transforming only against the data-tables and not against the system-tables.
An maybe more performant alternatively may be to use:
which could replace the values from the system-tables. Means in your case to replace the join from the routine with a loop over the field-values and loading it into mapping table.
Personally I wouldn't do something like this because for the most keys I don't need and want an autonumber() and if I would tend to create them within the origin load. Not each key must be an autonumber() especially not if those real-information are also needed which then usually leads to keep the origin field and having an autonumber(). Possible yes, really much benefit, hmmm ...
- Marcus
Hi Marcus!
Now thinking this with your POV makes more sense about taking control of what I'm going to autonumber(), like you said, it's a process to be self-conscious about the key definitions.
Thank you so much for your response!