Qlikview saves data in bad format in QVD

[#1]

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.

[dclark0699]

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;

[maxgro]

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

[#4]

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.

[dclark0699]

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.

[maxgro]

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.

[dclark0699]

Qlik Epic Developers Group‌

I'm just tagging the Qlikview Epic Group as I'm sure this is useful information for them as well