Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

mgolinvaux
New 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.

Tags (4)
1 Solution

Accepted Solutions
stabben23
Honored Contributor

Re: Problem with numbers with SQL select script

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‌

13 Replies

Re: Problem with numbers with SQL select script

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
Honored Contributor

Re: Problem with numbers with SQL select script

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
Honored Contributor

Re: Problem with numbers with SQL select script

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

just swith the two number and see what happens.

Re: Problem with numbers with SQL select script

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
Honored Contributor

Re: Problem with numbers with SQL select script

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

stabben23
Honored Contributor

Re: Problem with numbers with SQL select script

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.

Re: Problem with numbers with SQL select script

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

stabben23
Honored Contributor

Re: Problem with numbers with SQL select script

I wouldn't just text()

Re: Problem with numbers with SQL select script

Hahahaha make sense .

Community Browser