Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have had a heck of a time figuring out what was going on with values in my Qlikview application. I work in the healthcare field and we use something called ICD 9/10 codes to link diagnoses to patients.
These codes should be handled as varchar data because some codes are numeric (ex: 397) and some are alpha numeric (ex: V41.1). The problem I had was Qlikview would concatenate two codes together when it thought they were the same (ex: 397.0 and 397) became the same code. This caused problems with linking into the diagnosis table because two rows were being returned.
I spent a significant amount of time trying to use the text(), dual() and num() functions to try to fix this problem, but nothing worked no matter what I did.
It was not until I went back to our data architects (and spending 2 hours) that we realized Qlikview was storing the bad data into the QVD I was working from.
This means we had to code the dual(columnname, format) into the QVD load as once it was stored it could not be fixed by the application developers.
I spent a solid 4 hours trying to figure out why this was broken and just wanted to make sure there was an explanation for the next person.
We encountered this exact issue with ICD9/10 codes. I think when determining the column type for the QVD, Qlikview likely profiles the first x number of rows. So if they are all numeric, it becomes a number field.
What I did to work around it was an inline table load of a text value into the column that will store the code and then dropped that table after loading the data I wanted.
For example
_TEMP:
LOAD TEXT(DX_CD) as DX_CD INLINE [
DX_CD
397.0
V41.1
];
//Load Diagnosis info
DROP TABLE _TEMP;
I think the text function works if you use it before the store in qvd.
SET ThousandSep='.';
SET DecimalSep=',';
t:
load * inline [
icd
397
397,0
397,00
397,000
397,0000
397,00000
397,000000
] (delimiter is \t);
store t into t.qvd (qvd);
u:
NoConcatenate load text(icd) as icd inline [
icd
397
397,0
397,00
397,000
397,0000
397,00000
397,000000
] (delimiter is \t);
store u into u.qvd (qvd);
if you open the qvd with textpad you see (at the end) some differences
Yes that is what we found. Once the data is stored in the QVD the text,dual and num functions no longer apply to the data. Very confusing.
It does for sure. We couldn't implement that exact functionality in our case though because we have a data-driven/function driven extract process from our source system. So I couldn't have a Load statement on top of the initial SQL.
You can generate dynamically the sql select and the load using the metadata from the database; I did once for a sql server db; when the type of the field was nvarchar / ntext the extract generator added a text(field) to the load.
I found it's useful when you have a lot of little tables to load and don't want to type load text(...) for some days. The bad was it exctracted all the fields.
I'm just tagging the Qlikview Epic Group as I'm sure this is useful information for them as well