QlikView is converting text to a number when it should not

[#1]

This is a very critical and annoying problem.

I have a table where Code field can have values like 1234 and 01234. They are actually different values but when I load to QlikView it only loads one of them (the first that is find).

So, in other words, let's say this is the value I have in the database:

Code

----------

01234

789

05647

1234

0789

5647

When I load them to QlikView, I get this only:

Code

-----------

01234
789

05647

If I add a character to the field, like a |, it does return all values but I cannot have that as a solution.

So, how to force QlikView to NOT convert the values to anything and really understand them as unique?

[vardhancse]

Qlikview by default will store the unique values in the back end, but will display all the values until we mentioned distinct in the front end

[johnca]

Yes, as Sasi mentioned. Look at the table in the table viewer (Ctl T) and then do a Preview of that, you'll see all the values are there.

--john

[#4]

QlikView is not storing the unique values anywhere. If I do a Join from that table to another table using that Code field, it will only bring the records that matches the ones that are being returned not for all of them.

[eduardo_sommer · Accepted solution]

Hi Igor,

Use load text(Code) as Code

This will differentiate 0789 from 789

Don't forget to do this for every table where you load this code from.

Eduardo

[vardhancse]

while loading any any table in to qlikview:

in the front end we can see all the data not only unique values.

But while storing internally it will store only unique values.

Yes join between 2 tables, will load the related records.

[johnca]

I see now. Interesting dilemma. It appears QlikView sees both 01234 and 1234 as the same thing, even if you format as text a la text(value). It then displays just the first formatted version it found. If you alter the order of the values it changes.

--john

[johnca]

Interesting...this method works on the first load only, so I had to recreate the data in a csv (or excel). When I tried an inline load it would not work.

Thanks Eduardo, for pointing me toward this thread: http://community.qlik.com/thread/58699

--john

[maxgro]

in my test it works in inline load

t:

load text(Code) as Code;

load * inline [

Code

01234

789

05647

1234

0789

5647

];

[#10]

That works, thanks!