topic Re: Subroutine to AutoNumberHash128 keys in QlikView

Subroutine to AutoNumberHash128 keys

axnvazquez — Tue, 26 Oct 2021 08:41:48 GMT

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!

Re: Subroutine to AutoNumberHash128 keys

MarcoWedel — Tue, 26 Oct 2021 19:15:35 GMT

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

Re: Subroutine to AutoNumberHash128 keys

axnvazquez — Wed, 27 Oct 2021 06:29:17 GMT

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.

Re: Subroutine to AutoNumberHash128 keys

marcus_sommer — Thu, 28 Oct 2021 15:51:08 GMT

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:

Map ‒ QlikView

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

Re: Subroutine to AutoNumberHash128 keys

axnvazquez — Fri, 29 Oct 2021 08:20:05 GMT

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!