topic leading zeros in QlikView

leading zeros

Fri, 16 Oct 2009 23:57:53 GMT

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

leading zeros

Sat, 17 Oct 2009 00:04:50 GMT

You can try to eliminate zeros

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

you can add the number of zeros

text

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

leading zeros

Sat, 17 Oct 2009 00:07:07 GMT

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

leading zeros

Sat, 17 Oct 2009 00:10:27 GMT

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

leading zeros

Sat, 17 Oct 2009 00:16:44 GMT


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

leading zeros

Sat, 17 Oct 2009 00:23:55 GMT

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

leading zeros

Tue, 13 Jul 2010 17:38:31 GMT

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?

leading zeros

johnw — Tue, 13 Jul 2010 20:07:36 GMT

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

leading zeros

Tue, 13 Jul 2010 21:52:05 GMT

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

leading zeros

Tue, 13 Jul 2010 21:56:24 GMT

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

leading zeros

Tue, 13 Jul 2010 22:09:20 GMT

Yeah, I checked for that. Unfortunately for me, I am a consumer of the data. To further complicate matters, both the parent and child have two fields (both varchar) that indicate a records uniqueness, so I am already creating a composite key manually for QV. Part of the key is plan codes for medical plans. The application allows them to manually enter the plan codes which will be used as the primary key to be perpetuated. The database has a constraint on the plan code. The field is varchar however, so when one user enter "HYN" and another " HYN", the constraint allowed it.

The life lesson I have had to explain this to my business users several times: QV does not cover/eliminate underlying data or system issues; it probably exposes them faster.

Thanks Bruce

As an aside, the contributors on these forums have been an invaluable resource for the developers at my company. We have only been using QV for about 3 months and are trying to get up to speed.

leading zeros

Mon, 28 Mar 2011 12:08:53 GMT

In this case how we diffentiate these numbers as individual..Is there any way..I want to keep 001234,01234 and 1234 number as different numbers.By default QV will assume all these number as one i.e., 1234

pls provide the solution.

leading zeros

Anonymous — Mon, 28 Mar 2011 12:17:42 GMT

Hi,

I fixed this same problem bij loading the it as text.


text(FIELD) as FIELD

leading zeros

Miguel_Angel_Baeyens — Mon, 28 Mar 2011 12:19:02 GMT

Hello,

Try

Text(NumberField) AS NumberField

That will load 001234 as a string different to 1234 or 00001234.

Hope that helps

leading zeros

Mon, 20 Feb 2012 16:37:39 GMT

Hello everyone,

sorry to dig out an old thread, i wanted to avoid opening yet another thread concerning leading zeros in the dataset.

The suggestions

load text(column) as column;

sql select column from table;

seem to be the canonical answer to the question so far in the threads i found. For this example (extracting a column 1:1 into qlikview), it works perfectly.

However, i have a slight problem in my script, as a lot of functions are called in sql.

For example, one line in my script right now is:

sql select nvl(possible_entry1,possible_entry2) as used_entry from table;

(nvl is an oracle function that gives possible_entry1 if it is not NULL and possible_entry2 otherwise).

Now i have the same problem again: possible_entry1 and possible_entry2 can have "1" and "001" meaning different things.

however:

load text(used_entry) as used_entry;

sql select nvl(possible_entry1,possible_entry2) as used_entry from table;

gives an error when executing the "sql"-Line:

Field not found - <used_entry>

How can I apply the Text-Function in this example?

"load text (nvl(...))" does not work because the Qlik-View does not know about nvl, this part is done on the oracle-side.

leading zeros

Miguel_Angel_Baeyens — Mon, 27 Feb 2012 15:52:12 GMT

Hi,

Remove the LOAD part and leave only the SQL part in your script. Note that fields are case sensitive in QlikView, and some drivers are not, so they will return likewise irrespective you use select FIELD or select field.

Hope that helps.

Miguel

leading zeros

Mon, 27 Feb 2012 16:19:49 GMT

Leaving out the load part gets me back to square one: The Leading Zeros are mixed together, meaning that two values "0003" and "3" in the data end up either as "3" in qvw.

If you want to preserve the leading zeros, you definitely need the "load Text()"-Part.

This has been solved, for example in:

http://community.qlik.com/message/44902#44902

http://community.qlik.com/message/44007#44007

http://community.qlik.com/message/2572#2572

My problem was that i thought this solution would not work if the column is not loaded directly (e.g. "sql select columname from table"), but as part of a function (e.g.: "select nvl(column1, column2) as somecolumn").

The mistake was that (with the oracle-driver) the columns are returned all in Upper Case.

So the suggestions from the other threads still hold true, if you want to preserve leading zeros, the easiest working solution i found so far is:

LOAD TEXT(SOMECOLUMN) AS SOMECOLUMN, COLUMN3, COLUMN4;

SQL SELECT NVL(COLUMN1, COLUMN2), AS SOMECOLUMN,COLUMN3,COLUMN4 FROM TABLE;

The solution works for me for all columns i had trouble with when creating the thread.

Thanks again to everyone involved, especially for jagan for pushing me in the right direction and qlikuser14 for spotting the real error in my script (forgetting that qlikview is case sensitive while oracle is not).

Re: leading zeros

Miguel_Angel_Baeyens — Mon, 27 Feb 2012 16:29:12 GMT

Hi,

That is right, you either need the Text() or Num() function to keep leading zeroes.

Num(FieldName, '00000') // i.e.: in case your code is fixed to 5 digits

But removing the LOAD will help you check the names as they are retrieved by the driver and put into QlikView memory, which sometimes helps to understand where is the renaming problem (SQL or LOAD).

Regards.

Miguel