Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi Amine,
See comment swuehl in this topic hash128() without knowing the number of fields. Probably this is what will help you.
Regards,
Andrey
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
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