Strange behavior in Developer when loading different number strings

[#1]

Seems like "number looking" strings are being converted by Qlikview during the execution of load script.

e.g.

'001' is converted to '1' if there's another row with value '1' before it.

'1' is converted to '001' if there's another row with value '001' before it.

This is causing some very unexpected result in my actual dashboard. Can someone help me understand why this is happening and how to avoid it?

Here's the code:

X:
LOAD * INLINE [
Id, Code
X1, '001'
X2, '200'
X3, '1'
];

Y:
LOAD * INLINE [
Id, Code
Y1, '1'
Y2, '200'
Y3, '001'
];

EDIT:

Thanks for all the suggestions. To counter this issue where a SELECT statement is used, I ended up using a "Preceding Load" statement to force the columns to be text, using "Text()" function as suggested, before it's saved to Qvds. It seems to be working.

[Miguel_Angel_Baeyens · Accepted solution]

Hello,

First, you cannot do any conversions or use any functions in inline tables, the values you write are literals (spaces, commas). You will have to load resident and then transform them. Meaning that if the first value QlikView reads from the inline table is understood as number, all values for that field will be so dealt. Likewise, if your first line was

X1, A

All following values for Code will be "strings" for QlikView. Then you may discard this dummy value.

As a note, INLINE tables are good for some purposes, but their behavior in regards to nulls, empty and numeric values is not the same as it is for the other way of loading data into QlikView.

What it seems to me it's happening is that you need to convert your Code field to text, so preceding zeroes are respected. QlikView doesn't have data types as they are usually set when you create a table in SQL, although you can transform them using several functions.

Following your example, your code should look like this

X:LOAD Id, Text(Code) AS CodeFROM FILE.QVD (qvd);

Now your Code field will have those leading zeroes. The example is loading from a QVD file, you can set your own SQL SELECT statement.

Hope that helps

[#3]

Only as a comment but not a solution:

I had the same problem some time ago (and still have no solution) and learned, that QV defines the datatyp automatically by the first record comming from the data source. If it finds a text (as in your inline statement) it uses text, if it finds something that is interpretable as number it cuts leading zeros and takes number. If you load from an oracle table (without formatting function) for example a 001 would always be cut to 1, but if first value is "abc" and second 001 the leading zeros wold survive. I had the problem that I load many tables from Oracle with a loop and sql select * from statement and got missmatches in ID field values between different tables depending of the first record you load with the sql.

forced fomatting with text() or num() works as long as you can specify the fieldname and do not use "*" for load.

I never found a way to tell QV to produce a forced text load...

Christian

[#4]

This is quite normal for Qlikview - as it only stores one occurence of a value internally it regards 1 and 001 as the same (numeric) value.

Just force it to load as text using:

text(fieldname) as fieldname

then 1 and 001 will be stored as '1' and '001' respectively.

Regards,

Gordon

[#5]

But what to do if you need to load with "*" Because you load for example several tables with unknown field names? I have this case when I download a set of oracle tables to be stored in QVD_files. Then you can not use text() function.

Regards

Christian

[#6]

Hi all,

I have even a more complex and strange problem so solve:

we have two different items with their number 100-001-123 and 100-1-123.

In QV they are all treated as 100-001-123, although I read them from an MSSQL table using the text() function.

Removing the text(function) will not work, since then the items like 0000100 and 100 will be treated as the same.

Anyone an idea how to solve this?

Regards,

Gert

[Miguel_Angel_Baeyens]

Hi Gert,

First, I'd recommend you to create a new thread, given this one was OK more than one year and a half ago...

Second, where are you using the Text() function and can you share an example of your code here?

Regards,

Miguel