Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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
try
text(FIRM) & '|' & text(WKNR) & '|' & text(BEZW)
text(FIRM) & '_' & text(WKNR) & '_' & text(BEZW)
it may help you!
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.
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
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