topic Re: Part number as text, leading zero issue in QlikView

Part number as text, leading zero issue

martynlloyd — Mon, 08 Sep 2014 15:24:34 GMT

I have a table of part numbers (Parts Master)

Within this table I have two part numbers:

00119916, description "Oil Seal"

0119916, description "Seal"

I output the parts master to a QVD file in a ETL script

The problem occurs when I read this file in my application, and join it to sales (or anything)

QV treats both parts as 0119916

I would understand if it treated both parts as 119916, but why is it droping one of the leading zeros?

Many thanks,

Re: Part number as text, leading zero issue

Mon, 08 Sep 2014 15:27:21 GMT

Hi,

On your load script use the text function

load text(UPN) as UPN

Richard

Re: Part number as text, leading zero issue

Mon, 08 Sep 2014 15:28:08 GMT

Sorry, that shoudl force it to accept the string, as a string, and stop trying to parse it as a number.

as in

load TEXT(F1) as UPN, F2;
LOAD * INLINE

[
F1, F2
0123, part 1
123, part 2
]

;

Richard

Re: Part number as text, leading zero issue

martynlloyd — Mon, 08 Sep 2014 16:30:31 GMT

Hi, yes I know, but my questionis why it still shows one of the leading zeros?

Re: Part number as text, leading zero issue

martynlloyd — Mon, 08 Sep 2014 16:50:02 GMT

To clarify futher:

here is my SQL table

here is the QVD file, stand alone

As you can see, it has interpreted 00119916 as (0)119916, numeric, but why is it showing a leading zero?

Is it somehow due to the order in which the fields are loaded, and QV's dual type fields?

Many thanks,

ML.

Re: Part number as text, leading zero issue

JonnyPoole — Mon, 08 Sep 2014 19:38:21 GMT

does seem strange. Is the script that specifically generates the qvd wrapping the field in text() as well? or just the script that loads from the qvd? In the qvd creating script is the text() transformation done on the primary load and not say in a preceding load or subsequent resident load? Just looking for some of the details.

Re: Part number as text, leading zero issue

maxgro — Mon, 08 Sep 2014 19:49:27 GMT

I think you get the same number 119916

with the format of the first you load, 0119916

to check try to change the order in sql and see if the format is 00119916

add text as already suggested

Re: Part number as text, leading zero issue

Colin-Albert — Mon, 08 Sep 2014 19:49:32 GMT

QlikView holds all data as dual values. This means each field value has a text and number equivalent Your data load encounters 0119916 first so stores this as the number 119916 with the equivalent text 0119916. When the value 00119916 is encountered, QlikView already has a value for the number 119916 in the data array for that field, so a pointer is added for the new row linking to the existing dual value (0119916, 119916). The actual value 00119916 is in effect ignored.

To force QlikView time correctly recognise the leading zeros you can force the data to be recognised as text by using the text() function. This will then store 119916, 0119916 and 00119916 as separate values in the same field.

Re: Part number as text, leading zero issue

Tue, 09 Sep 2014 08:13:34 GMT

Colin,

I would concur with this analysis. We just had a similar issue where a value from a log file was read in as 1%, subsequent values of '1' also stored as '1%'

Richard