topic Re: QV knows better - issue with field format in QlikView

QV knows better - issue with field format

Wed, 21 Dec 2016 12:55:51 GMT

Hi,

During support phase of my application one of users found interesting issue.

QV when loading data overrides source data formatting - when it should be text, QV takes number format. What is more interesting, format is aligned to first row entered into the field.

Example:

I have xlsx file with following data, those are strings stored in text column, which can be interpreted as numbers in different formats.

ID	TEST_NUMBER
1	1E0
2	01
3	001
4	0001
5	00001
6	0001.00
7	1.000
8	01.00000000
9	1 zł
10	1

Now when data is loaded to QV application is using first used format and displaying following:

ID	TEST_NUMBER
1	1E0
2	1E0
3	1E0
4	1E0
5	1E0
6	1E0
7	1E0
8	1E0
9	1E0
10	1E0

I know that there is a workaround (Leading 0 in Text field Qlikview transforms data incorrectly) but is there any other smart way to turn off this 'intelligent' behavior? I just want to see original input, without rebuilding completely all my scripts.

Thanks in advance for help. Attached QV and Excel files for reference.

Best regards,

Piotr

Re: QV knows better - issue with field format

sunny_talwar — Wed, 21 Dec 2016 15:01:37 GMT

How about loading it like this:

LOAD ID,

Text(TEST_NUMBER) as TEST_NUMBER

FROM

[TEST_FILE.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: QV knows better - issue with field format

Thu, 22 Dec 2016 08:46:49 GMT

Hi Sunny,

Thanks for reply, that's the workaround I mentioned. To be precise - this will not work with SQL loads I'm using with my applications. First I have to load empty table with all fields, then enter data with SQL query. Due to this my scripts will be much much longer, as I have to put a lot of empty tables first before loading the data.

So my scripts for every table looks like:

Table1:

LOAD

ID,

Text(TEST_NUMBER) as TEST_NUMBER

;

SQL SELECT

ID,

TEST_NUMBER

FROM

SOURCE_TABLE

;

What I'm looking for is permanent solution (turning off/on some option, changing any setting), if it doesn't exist I have to spend some time to amend all my scripts.

Best regards,

Piotr