topic Re: Problem with numbers with SQL select script in QlikView

Problem with numbers with SQL select script

mgolinvaux — Fri, 17 Nov 2017 09:36:56 GMT

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.

Re: Problem with numbers with SQL select script

sunny_talwar — Fri, 17 Nov 2017 13:02:46 GMT

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);

Re: Problem with numbers with SQL select script

stabben23 — Fri, 17 Nov 2017 13:21:05 GMT

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‌

Re: Problem with numbers with SQL select script

stabben23 — Fri, 17 Nov 2017 13:26:55 GMT

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

sunny_talwar — Fri, 17 Nov 2017 13:31:06 GMT

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

0062

0061

];

Re: Problem with numbers with SQL select script

stabben23 — Fri, 17 Nov 2017 13:39:06 GMT

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

Re: Problem with numbers with SQL select script

stabben23 — Fri, 17 Nov 2017 13:46:57 GMT

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

sunny_talwar — Fri, 17 Nov 2017 13:59:36 GMT

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

Re: Problem with numbers with SQL select script

stabben23 — Fri, 17 Nov 2017 14:06:17 GMT

I wouldn't just text()

Re: Problem with numbers with SQL select script

sunny_talwar — Fri, 17 Nov 2017 14:07:12 GMT

Hahahaha make sense .

Re: Problem with numbers with SQL select script

mgolinvaux — Mon, 20 Nov 2017 13:48:50 GMT

Hello,

Unfortunately, the WORK_ID is not always a 4 digit number, sometimes it's more.

Regards.

Re: Problem with numbers with SQL select script

mgolinvaux — Mon, 20 Nov 2017 13:51:21 GMT

Hello,

That's exactly the problem!

Some data in my source table contains "62".

I will solve this in the source table and the script should be working fine.

I just need to wait tomorrow because the source table is reloaded one time a day.

Regards.

Re: Problem with numbers with SQL select script

mgolinvaux — Tue, 21 Nov 2017 12:18:46 GMT

Hello,

I can tell now that was exactly the problem. After suppressing the values with "62", my script works fine and load the lines with "0062" without problem.

Thanks!

Re: Problem with numbers with SQL select script

stabben23 — Tue, 21 Nov 2017 12:24:11 GMT

Great, QlikView is sometimes to smart for it self