topic Re: How to apply a hash function to all fields in QlikView

How to apply a hash function to all fields

Wed, 25 Nov 2020 16:16:04 GMT

Hi everyone,

I'm new in QV community. I'm trying to load all fields from a QVD and i want to apply the hash function.

Let's assume that we have a high number of fields.

QlikView doesn't accept this syntax : hash128(*).

Can anyone tell me how to bypass this ?

Thanks

Re: How to apply a hash function to all fields

ahaahaaha — Fri, 10 Mar 2017 14:45:06 GMT

Hi Amine,

See comment swuehl‌ in this topic hash128() without knowing the number of fields‌. Probably this is what will help you.

Regards,

Andrey

Re: How to apply a hash function to all fields

Peter_Cammaert — Fri, 10 Mar 2017 16:07:59 GMT

There is no real "bypass" for this. Hash128() will accept a variable number of expressions, but * is not an expression. It's just a wildcard character that you can only use in very specific places (and not as a parameter)

The best thing to do is - as suggested in many other discussions - to enumerate your column names and glue them together in a comma-separated string, then use $-sign expansion to put that list of column names as parameter list in a call to Hash128().

Fortunately you're trying to read from a QVD. QlikView offers very useful functions to lift the metadata from a QVD file (without reading the entire QVD row set). See File functions ‒ QlikView (everything that starts with Qvd is of interest to you).

Imagine that you have a QVD file file called HugeDataFile.qvd in the current directory. The following code can be used to create any parameter list from an unknown number of columns:

SET vQVDPath = .\HugeDataFile.qvd;

// First one here to avoid comma mess

LET vFieldNames = '[' & QvdFieldName(vQVDPath,1) & ']';

// Add all others separated by commas

FOR i = 2 TO QvdNoOfFields(vQVDPath)

LET vFieldNames = '$(vFieldNames), [' & QvdFieldName(vQVDPath, i) & ']';

// Now use the content of vFieldNames in a function call or a LOAD statement

BigTable:

LOAD *, Hash128($(vFieldNames)) AS TableHash

FROM [$(vQVDPath)] (qvd);

Note that calculating hashes on a large number of column values is not the most efficient way to use cpu cycles and/or RAM. Are you sure you cannot use a known primary key to streamline this process?

Best,

Peter

Re: How to apply a hash function to all fields

Mon, 13 Mar 2017 08:37:43 GMT

Hi Peter,

Thank you for your answer that's exactly what i was looking for.

And no, i cannot use a known primary key to streamline this process for the simple reason that i have a variable number of QVD files in input. Also, the primary key column is not always in the first order. That's why am looking to make a hash column. But if you have a better idea to apply in my case than using hash function it would be great.

ManyThanks,

Amine