Qlik Community

Deployment Framework

Announcements
June 28, 10AM ET: Qlik Nation and Qlik Community present: CyberSleuth REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
aritting
Creator
Creator

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

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

leading zeros.jpg

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

1 Solution

Accepted Solutions
jonathandienst
Partner
Partner

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

];

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

3 Replies
micheledenardi
Specialist II
Specialist II

Hi Andrew,

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

Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.
rahulpawarb
Specialist III
Specialist III

Hello Andrew,

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

Regards!

Rahul Pawar

jonathandienst
Partner
Partner

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

];

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein