Leading 0 in Text field Qlikview transforms data incorrectly

[#1]

I am having an issue where Qlikview is acting oddly. I have a part number field from the data base that is a text field. Occasionally a number is entered here. Qlikview is then doing the following:

Part in Database:

08491710

8491710

These are two separate Part numbers.

Qlikview displays:

Part in Qlikview:

08491710

This is causing issues now. I have discovered that this happens soely in Qliview because loading the field like: text(partnum) as partnum will display the correct values. However, the first time I load this table, I store the entire table into a QVD using just:

Select *,

From ......;

Store ... into ...;

This is then converting it with qlikview so that I can no longer apply the text command to this field. I tried loading from the QVD using text(partnum) as partnum and it does not show correctly. Is this another situation where I will have to put the entire Select statement into a Load statement and apply text() to every field that should be a string?

This seems a bit ridiculous as this makes it so I can no longer user the * command, and now have to manually figure out what fields are text.

Note: I tested verbatim and it has no effect on this issue.

[jonathandienst · Accepted solution]

Hi

QV is interpreting the field as a number and once converted the leading zero is lost. This is the same as Excel.

the only way (that I know of) is to use text() in your initial load as well. Only fields which look like numbers but should be treated as text will require this.

You can use *, but will need one extra field for text(partnum) and jump through a couple of extra hoops:

LOAD *,

     Text(partnum) As TPartNum

     ...

FROM ...;

DROP Field PartNum;

RENAME Field TPartNum to PartNum;

Regards

Jonathan

[#3]

It is actually the opposite of what you said. It is not dropping the leading 0, it is adding it to the part that does not have a leading 0. I am pretty sure it is the same thing however.

for this portion of your script:

DROP Field PartNum;

RENAME Field TPartNum to PartNum;

I would place this after the SELECT statement and before the STORE statement correct? I have a lot of cases where all I do is

Temp:

Select *

From ....;

Store ... into ...;

Final:

Load abc,

bbc,

ddc

Resident Temp;

Drop Table Temp;

I am assuming in these cases I will need to add a Load section to the Temp table to convert these fields to text, and then store it so that I do not lose the data during the store. Do you think Qlikview is smart enough to maintain this field whenever I load data from the QVD after this, or will I this still need to apply the Text() function any time I load from the QVD as well?

Qlikview's handling of leading/trailing spaces and 0's is becoming a major pain...

[jonathandienst]

Hi

Actually it is dropping it - but the text representation is keeping it on display. On loading QV is deciding that it should be displayed with a leading zero, based on the format of the input data. So you see a leading zero, but Qv is handling it as a number, in which the leading zero plays no part. 08491710 is represented internally as the number 8491710.

Qlikview 'sniffs' the format of the input data and sets the field format based on the results. In your case, it has concluded that the field is numeric, but you want it displayed with a leading space.

Essentially all numbers are 'duals' with a text format and an numerical value. The text format is what is displayed, the numerical value is used for comparisons, sorting, and in expressions. By default, most numbers are displayed just the way you expect, but this can be overridden, as is the case here. The text() function in an expression retrieves the text version of the dual value, and num() function returns the numerical. Dates are simply numbers with a date format applied.

I think it may be unnecessary to apply text() when reading from the QVD as the QVD includes the QV data type and format, although you should test this to be sure. Of course this assumes that you used text() when you did the initial read to create the QVD. There is no data type when reading from a text file, so QV relies on its default behaviour.

Hope that helps to explain what's happening here

Regards

Jonathan

PS:

Many people would consider it poor practice to have 2 IDs 08491710 and 8491710 representing different things. Your example illustrates why. Of course, I assume that this is input data that you just have to deal with...

[#5]

Yes this helps explain what it is doing. It is still annoying though when I am storing entire tables and am not completely aware how a field is entered/ when it should be defined as a string. I just have a lot of research and typing ahead of me. This will be a good excuse to also apply rtrim() everywhere since I have been putting that off as well. (Our ERP software drops only trailing spaces, and keeps leading spaces)

as for the PS I totally agree that this is bad practice. I am assuming that it was entered as a mistake, however it was used so we can not remove it. This is also the case where someone accidentally hit space before entering a part so now we have 2 identical parts, one with a leading space one without that are causing issues.

One more question, I asked this in another post, but are you aware if transforming index fields in the select statement will slow down load speeds, or is this only the case if I transform an index field in the where clause?

[jonathandienst]

I dont understand what you mean by 'transforming index fields'.

Jonathan

[#7]

I mean like applying rtrim() to an indexed field. I know indexed fields load much faster, just was not sure if adding a function to these fields will reduce the load time. I am doing several Incremental loads with the Add,Update,Delete method using Inner Join SQL SELECT PrimaryKey FROM DB_TABLE; Since I need to keep leading spaces and drop trailing I will need to change it to Inner Join SQL SELECT rtrim(PrimaryKey) asPrimaryKey  FROM DB_TABLE;

I just want to make sure that I am not exponentially increasing load times by doing this.

[jonathandienst]

Hi

Too many variables to answer this definitively. It is likely to slow it down, but I think only testing will tell how signifcantly.

But if I understand you correctly, try this script instead.

Inner Join

LOAD rtrim(PrimaryKey) As PrimaryKey;

SQL SELECT PrimaryKey FROM DB_TABLE;

I don't think the load is affected much by the rtrim.

Jonathan

[#9]

This is actually what I ended up doing for both of my issues with rtrim and text functions. Since text() must be used in the load section for it to work correctly, I just apply rtrim(text(field)) as field for every field. Since luckily our ERP software has a program that lists out all the fields in a table with what type of data they are, I was able to easily export it to excel and create the entire load script based on formulas. This does make my script extremly long, however it is a simple copy past, copy pase  to get the load section entered.

The only difficult part is that since I don't want to create completly new QVD's, I have to update all 15 of our documents scripts before updating the QVD's. Sometimes the reload button is a little too close for comfort.

[#10]

Didn't get it - how to force the initial load (SELECT FROM ....) result with TEXT field - the "TEXT" keyword is legal only when loading from QVD not while selecting data with SQL.