topic Re: Item numbers recognition... in QlikView

Item numbers recognition...

mato32188 — Wed, 23 Apr 2014 06:03:54 GMT

Hi,

we have records of items starting with 000XXX (varchar).

Problem:

Frontend users see two different items, first one as 000111, second one as 0111. Those are two different Items with other characteristics in other tables. Qlikview shows such item numbers as 000111 for both of them.

Is it considered as "number 111"? Why are there automatically added zeros? We tried perform pure SQL select in MS Sql man. studio, wasnt any problem to recognize 0111 and 000111.

Could anybody knows to solve this issue?

Thanks a lot

Re: Item numbers recognition...

tresB — Wed, 23 Apr 2014 06:06:49 GMT

Try using text() while loading the data.

Re: Item numbers recognition...

jonathandienst — Wed, 23 Apr 2014 06:08:27 GMT

Yes, it is considered a number. Qlikview infers the data type (numerical in this case) and the display format ("000000" in this case) from the first instance of that field.

To keep them separate, force QV to load that field as text, like:

LOAD text(ItemId) As ItemId,

...

HTH

Jonathan

Re: Item numbers recognition...

MK_QSL — Wed, 23 Apr 2014 06:09:01 GMT

You can use below in QlikView Load Script

Load

Field1,

Field2,

TEXT(YourIDField) as YourIDField;

SQL Select * From .....;

Re: Item numbers recognition...

cfz — Wed, 23 Apr 2014 06:11:26 GMT

Hi Martin.

From the Frequently Asked Questions in the Reference Manual:

Q: Why are for instance '002', '02' and '2' sometimes interpreted as the same value by QlikView?

A: All the values in the example share the same numeric value in QlikView. As a rule, QlikView will try a

numeric interpretation of any data. If a numeric interpretation is possible, it will be used for the association.

If you use the interpretation function text() on a field in the script, however, the values will be treated

strictly as text values. The values in the example above will then be interpreted as three different values.

I hope it helps

Carlos