Problem with numbers with SQL select script

[mgolinvaux]

Hello, I encounter a problem when I load data from an Oracle table with Qlikview.

The source table contains a column with an ID and an other with a part of this ID (last four numbers or more).

Example:

ID	LastNumbers
BE00120061	0061

When i load this table, i use this script:

Table:

SQL SELECT *

FROM "DB".TABLE;

store Table into TABLE.qvd(qvd);

The problem is, some data from the "LastNumbers" column are not loaded correctly when the number start with "0", like this:

The LastNumbers "62" should be "0062" like in source table:

That's really strange and totally illogic i think.

When i load only the line with ID "BE00120062"  whith a "WHERE" clause, the number is loaded correctly:

I tried to force the text format with text() function but that's not working...

This script have been tested with Qv 11.20 SR15 and QV12, the result is exactly the same.

I think there is a bug here...

Can someone helps?

Thank you.

[sunny_talwar]

Is WORK_ID always a 4 digit number? If it is, then may be this

Table:

LOAD Num(WORK_ID, '0000') as WORK_ID_NUM,

      *;

SQL SELECT *

FROM "DB".TABLE;

store Table into TABLE.qvd(qvd);

[stabben23 · Accepted solution]

Hi Maxime,

Qlikview will remeber the first value, so if qlikview find 62 and then 0062 "he" think that 0062 will be 62. But if "he" find 0062 first, this will be keept as 0062. All numbers With leading 00 should be format as text to be sure that the leading part stays. I'm pretty sure that somewhere in Your Field WORK_ID have the number 62.

You can do as stalwar1 suggest, but I'll think you have to format it as text after that.

text(num(WORK_ID,'0000') as WORK_ID‌

[stabben23]

Here is a simple qvw documet to show what I mean.

just swith the two number and see what happens.

[sunny_talwar]

Why do you need text? I mean if you wish to convert it to text, then yes, but this should work

LOAD RowNo() as Key,

*,

Num(value, '0000') as NewValue;

LOAD * Inline [

value

62

0062

0061

61

62

];

[stabben23]

Correct stalwar1‌ text is not needed, and it also shows that he keeps the first readed value as I metioned.

[stabben23]

Just to clarify why I use text, I have met problem where Companys have ex itemnumbers created like this.

ex 62, 062, 0062, this must be handled as text.

[sunny_talwar]

Well in that case, why would you use Num()

[stabben23]

I wouldn't just text()

[sunny_talwar]

Hahahaha make sense .