Qlik merging leading zero values with non leading zeros into 1 distinct leading zero value not desired

[aritting]

I have an issue with QlikView 11.20

In a column called Lot Num that in the source system is a 30 spaced character field. There are values that are numbers and alpha/numerica values.  My specific issue is that QlikView is merging two distinct values into one.  It is merging 0001155859 and 1155859 into 0001155859.

LOAD * INLINE [

    "Lot Num", "Order Num"

    01, 1

    1, 2

];

results in

I've seen it other discussions to use the TEXT() function but I am loading from a QVD. and putting that function over the load statement of the qvd does not distinctly separate the values.  Is there another method to tell Qlik not to merge the two fields together and treat 01 and 1 as different?

I am experiencing this as similarly described in this blog post QlikView Addict: When QlikView Data Types Go Wrong

[micheledenardi]

Hi Andrew,

use num([Lot Num],'0000000000') as [Lot Num] if you want to merge 0001155859 and 1155859 into a single value 0001155859.

[rahulpawarb]

Hello Andrew,

Could you please share the sample QVD file? This will help us to identify the issue.

Regards!

Rahul Pawar

[jonathandienst · Accepted solution]

Qlikview/Sense use the first row to determine the display format for numeric values (numbers and dates). In your case, the first line has a leading zero, so Qlik assumes a numeric value with a leading zero for all other values in that field. '01' and '1' are equivalent values and are both loaded as '01'.

This is usually a good assumption. In your case, 01 and 1 are distinct values, so you will want to treat them as text rather than numeric, so load with the text function. For your inline example,this would become

LOAD Text([Lot Num]) as [Lot Num],

  [Order Num]

INLINE [

    Lot Num, Order Num

    01, 1

    1, 2

];