Part number as text, leading zero issue

[martynlloyd]

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,

M.

[#2]

Hi,

On your load script use the text function

load  text(UPN) as UPN

Richard

[#3]

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

[martynlloyd]

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

M.

[martynlloyd]

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.

[JonnyPoole]

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.

[maxgro]

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

[Colin-Albert · Accepted solution]

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.

[#9]

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