Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Leading zeros issue

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.

1 Solution

Accepted Solutions
Not applicable

Re: Leading zeros issue

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 Replies
awhitfield
Esteemed Contributor

Re: Leading zeros issue

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

MVP
MVP

Re: Leading zeros issue

try

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

hariprasadqv
Contributor III

Re: Leading zeros issue

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


it may help you!

Not applicable

Re: Leading zeros issue

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.

Not applicable

Re: Leading zeros issue

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

Not applicable

Re: Leading zeros issue

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

Community Browser