Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to apply a hash function to all fields

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

1 Solution

Accepted Solutions
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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) & ']';

NEXT

// 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

View solution in original post

3 Replies
ahaahaaha
Partner - Master
Partner - Master

Hi Amine,

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

Regards,

Andrey

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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) & ']';

NEXT

// 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

Not applicable
Author

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