Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mgolinvaux
Contributor II
Contributor II

Problem with numbers with SQL select script

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

BE001200610061

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:

Capture.png

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

Capture2.png

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:

Capture3.png

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.

1 Solution

Accepted Solutions
stabben23
Partner - Master
Partner - Master

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‌

View solution in original post

13 Replies
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
Partner - Master
Partner - Master

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
Partner - Master
Partner - Master

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

];

Capture.PNG

stabben23
Partner - Master
Partner - Master

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

stabben23
Partner - Master
Partner - Master

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
Partner - Master
Partner - Master

I wouldn't just text()

sunny_talwar

Hahahaha make sense .