Leading zeros issue

[#1]

Hello all,

I'm having an issue with creating a key between files. I found out the problem is with leading zeros.

I have two files on Iseries which are loaded into Qvd files with Select *. Below you see a piece from our ERP system

One is FSTX

FIRM   WKNR  SPCD	FILE	BEZW
1          000       E	TABP	53001
3          000        E	TABP	53001
4          000       E	TABP	53001
1          000       E	TABP	53002

the other TABM

FIRM   WKNR  TXRT	TXNR
1          000      53	001
1          000      53	002
1         000       53	003

I'm was building a key from FIRM & WKNR & BEZW   against    Firm & WKNR & TXRT & TXNR

Because TXNR is a character field it shouldn't give a problem I thought.

but not everything was matching in Qlikview. I created a seperate file to see the key that was build.

Here you see the TABM file in Qlikview. Some leading zeroes are deleted and others are kept.

obviously if I create my key it will not match the other file. Can anyone explain to me why some are deleted and some not and even better how to keep the leading zeros?  I tried text(T3Txnr) but it has the same result. Also tried it in the SQL which loads the file but I got errors. Would the problem be in the SQL which loads the data in the QVD? any idea how to solve it there?

thank you for any hint to help me on my way.

[awhitfield]

Hi,

you need to load the values with leading zeros as text rather than number, then they wont disappear, e.g. in your load script.

Text(TXNR) as TXNR

[maxgro]

try

text(FIRM) & '|' & text(WKNR) & '|' & text(BEZW)

[hariprasadqv]

text(FIRM) & '_' & text(WKNR) & '_' & text(BEZW)

it may help you!

[#5 · Accepted solution]

Hello,

Thanks for the info.

Qlikview is setup to first pull all data to QVD files and the projects themselves load data from these files.

I was trying with text() in the file that creates the QVD files but that gave errors in SQL.

When I would use text() when I pulled data from the QVD file it didn't change anything. And I didn't know how to force this in SQL.

Now I used the Num() function. T3FIRM & T3TXRT & NUM(T3TXNR, '000')

i'm not sure if it is 100% correct but I couldn't find an error in the list.

thank you all for your help.

[#6]

Hi,

Did you get an answer to this problem?  We are having a similar issue whereby we pull data to QVD files and the data is then loaded from these files to the projects.  Some of the data is losing the leading zeros but not all. 

We have tried forcing it from SQL using CAST and also TEXT when loading into the project but without success.

Any help would be much appreciated.

Thanks

[#7]

Hello,

I solved it by using

NUM(T3TXNR, '000')

T3TXNR is 3 characters long. And this would add the leading zeros.

this solved my issue. I tried the text() function but didn't act as expected.

kind regards