leading zeros

[#1]

Hello all,

I am pulling together a report from a number of different sources. My key field joinng all my tables together is a number. In some of the data files it might be represented as 001234 and in another it will be 1234.

I am sure that this is a common problem and rather than try and re-invent the wheel I thought I would ask is what is the best way to solve this problem.

Thanks in Advance

Alan

[#2]

You can try to eliminate zeros

replace(ltrim(replace(fieldname, '0', ' ')), ' ', 0) as fieldname1

or

you can add the number of zeros

text

('00'&([fieldname])) as fieldname1

[#3]

Alan,

Fortunately, QV is intelligent enough to relate 0001234 and 1234. Still you can use num(key) across for the generalization or convert to numeric while bringign to QVD.

--Arun

[#4]

is the field named "001234" and "1234" or are the values (contents) of a field filled "001234", "1234", meaning the same key ...?

Best regards

Stefan

[#5]

Arun Paduva wrote:
Alan,
Fortunately, QV is intelligent enough to relate 0001234 and 1234. Still you can use num(key) across for the generalization or convert to numeric while bringign to QVD.
--Arun<div></div>

Yes, Arun is right, QlikView handles "001234" and "01234" and "1234" as the same value and matches the values correctly.

Best regards

Stefan

[#6]

Thanks all for your help, I had managed to remove all the leading zeros before the replies and it turns out that I have a different problem. I thought it was because the records weren't matching but if QV is clever enough to match then I need to look elswhere.

Thanks all

[#7]

What if I don't want QlikView to handle this situation? I have a scenario where " A123" represents "Apples" and "A123" represents apricots. When I move the data from the database into the QVD, QlikView drops the leading space off first value. This causes cartesians because my tables are orders and items. "A123" is the item_code which the two tables are joined on. I have tried coding "text(item_code)" but QV still drops the values. Is there a way to prevent QV from interpretting the data?

[johnw]

SET Verbatim = 1; // turns off automatic trim
SET Verbatim = 0; // turns back on automatic trim

[#9]

Thank you so much. Relatively new to QV and have gotten "surprised" by this a couple of times already. Thanks again.

[#10]

ALang

I would also think about changing to a new key field (either in QV or your source), the difference between "ABC123" and " ABC123" is very small & might be a source of frustration for the users.

Cheers

Bruce