topic SQL Load from Database incorrect data - Appending leading zero in QlikView

SQL Load from Database incorrect data - Appending leading zero

chris1987 — Sat, 16 Nov 2024 03:53:59 GMT

Hi,

I've hit a big problem that I can't figure out.

I have a table that I am doing an SQL Query on. Really, really simple.

The script I am using is:

TempItem:
SQL SELECT 
"Item-no",
"Full-Description"
FROM UKTABLE.PUB."item";

The field I am interested in is the "Item-no", this contains some item numbers with leading zero's and some without.

For example:

Item-No   |   Full-Description
-------------------------------------------------------
38033     |   Part 1
038033    |   Sub-Assembly 1

As you can see there are two part numbers.

But when I run the above code and put the fields in a table, I get the following:

Item-No | Full-Description
------------------------------------------------------- 
038033  | Part 1 
038033  | Sub-Assembly 1

*Note that Part 1 now has a zero at the front.

This is incorrect and is skewing my figures.

I know that QV stores the value as double, but I've tried this too with no success:

TempItem: 
SQL 
SELECT CAST("Item-no" as varchar(20)) AS "Item-no", 
"Full-Description" 
FROM UKTABLE.PUB."item";

And I've also saved the data to a .qvd and loaded it with the following, again with no success:

TempItem:
LOAD text([Item-no]) as [Item-no], 
Full-Description
FROM
[D:\Qlikview\UK\Data\Item.qvd](qvd);

Here is my SQL Database table, as you can see it's already in text format

Has anyone come across this before?

Cheers

Chris

Re: SQL Load from Database incorrect data - Appending leading zero

marcus_sommer — Wed, 10 Apr 2019 14:43:48 GMT

Try it in this way:

TempItem:
load text("Item-no") as "Item-no", "Full-Description";
SQL SELECT "Item-no", "Full-Description"
FROM UKTABLE.PUB."item";

- Marcus

Re: SQL Load from Database incorrect data - Appending leading zero

chris1987 — Thu, 11 Apr 2019 08:32:35 GMT

Thanks Marcus,

I've only done a small sample test and it appears to be working! So just running it on the full db now.

Do you know why this works? Would be good if you could explain a little or point me in the direction of an answer. Will be good to know in future.

Cheers

Chris

Re: SQL Load from Database incorrect data - Appending leading zero

marcus_sommer — Thu, 11 Apr 2019 09:00:47 GMT

The reason for this behaviour is that Qlik doesn't really know data-types else it interprets the data as numeric or strings - and this happens by the first loaded field-value and is then applied to the whole column. With the most of the columns it worked very smoothly and only by such cases with a leading zero or similar stuff you need to enforce a different handling which is usually done with a text().

- Marcus