Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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

Tags (1)
1 Solution

Accepted Solutions
Highlighted

Re: How to apply a hash function to all fields

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
Highlighted
Master
Master

Re: How to apply a hash function to all fields

Hi Amine,

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

Regards,

Andrey

Highlighted

Re: How to apply a hash function to all fields

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

Highlighted
Not applicable

Re: How to apply a hash function to all fields

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