Data load problem

[mcelleri]

Hi guys. I have this problem.

We have an Informix database; and one table have the information about our product's colours. In the table, there may be duplicate color codes; because we have several companies.

The database connection is through odbc.

Each record has the company code and the color code as its primary key. We also have a text type field; the problem we have is that when the information is extracted from the database to a qvd file; for some reason the text field that is loaded does not maintain the original data format as it's in the database.

For example, in one company there is color 00023, in the other there is color 023.
When the qvd file is loaded and examined both records are presented as 00023.

When extract the data, I use this:

SQL Select * from colour_table where state = 'A';

Store colour into [lib://QVDs/colour.qvd];
Drop Table colour;

Why does this happen and how can it be corrected?
I appreciate your help in solving this.

[Fabiano_Martino_Intelco]

Hi @mcelleri,

this behaviour depends on how Qlik Sense stores the values.

When you read the value 00023 Qlik stores the value and its numerical representatio (23) as a dual value.

When you read 023 it has already the number 23 in memory (the symbols table) so it considers 023 a duplicate of 23.

If you need to preserve the leading zeroes I suggest the Text() function.

A simple example:

Values:
Load *,
Text(Field) AS TextField,
Num(Field) AS NumberField;
LOAD * INLINE [
Field
01
1
2
3
0004
004
04
5
];

I also added the Num() function to get the numeric value of the field.

Regards

[mcelleri]

Thanks Fabiano.

Only one question, the use of text() put in later on another script section?

In the first load (directly from de informix database), the data loads 023 and 00023. I mentioned in my initial post;

when extract the data, in the QVD, loads wrong data.

SQL Select * from colour_table where state = 'A';

Store colour into [lib://QVDs/colour.qvd];
Drop Table colour;

In the select query, directly from the database; the text() function don't be resolve.

[Fabiano_Martino_Intelco]

Hi @mcelleri ,

I would put the Text() function in a LOAD directly above the SQL command towards Informix DB.

The function needs to be used on the data as soon as it is loaded.

If this doesn't work I ask you to post the related script section for a review.

Regards

[anat · Accepted solution]

colour:

load *,text(field) as new_field;

SQL Select * from colour_table where state = 'A';

Store colour into [lib://QVDs/colour.qvd];
Drop Table colour;

[Fabiano_Martino_Intelco]

Hi,

I tested this on QlikView 12.80.

I assume you are using Qlik Sense. The behaviour should be the same (since they share the QIX engine).

If possible,  can you post the qvd file (or just a part of it)?

Regards