Subroutine to AutoNumberHash128 keys

[axnvazquez]

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!

[MarcoWedel]

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

[axnvazquez]

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.

[marcus_sommer · Accepted solution]

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

[axnvazquez]

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!