Field Format

[Anonymous]

Hey folks,

I have a SQL-Table from which I need a field (TBRKEY).

Thus I request the data with:

TBPOSP_mit:
  load *; SQL select * from TBPOSP where TBTBGR = 'FRD' and TBSART = '122' and TBSPRA = '  ' and TBFIRM = '010';

I get the following result:

Attention to the format of TBRKEY!!!

But the whole where case is not possible in my specific case.

Thus I request the data with:

TBPOSP:
  load *; SQL select * from TBPOSP;

After selecting the correct fields I get the following result:

Attention to the format of TBRKEY!!!

Why are there zeros in front of the numbers?

In my opinion I should get in both cases the same result, but as you can see here it is not.

Any ideas why this behavior is like this and workarounds?

If this may help, the TBRKEY-field is saved in a SQL database with a field length of 10 and the field type is alphanumeric.

[swuehl · Accepted solution]

I assume that your source DB shows formats of TBRKEY with and without leading zeros.

QV will use the text format it first encounters (in load order) for any given dual value's numeric value.

[marcus_sommer]

Are you sure that you really looked on the same field-values respectively records by your made selections? To check add just a rowno() as RowNo within the preceeding-part and add this field to your tablebox and then check again.

- Marcus

[Anonymous]

Hej Marcus,

thanks for your fast reply.

But I load the data of the tables with two different SQL's.

     1. In case of the SQL with the where-cause I load only the nine rows.

     2. In case of the SQL without the where-cause I load around 350.000 rows.

For this reason I don't think the rowno() can help me.

But of course I tried it. See the result in the following screenshot.

1. without the where case:

2. with the where case:

Any other ideas?

- Emanuel

[Anonymous]

Hi Stefan,

thanks for your fast reply.

This was also my first gist. Therefore I checked my Database.

I have many different values (without the where-cause) in TBRKEY, but in the DB is never a leading zero in front of the numbers.

Now I checked again the values in TBRKEY and there are also numbers which are smaller than 3 digits (e.g. "23") and there are no leading zeros. In the TBRKEY-field are also numbers that are much longer (e.g. "700000000").

Hopefully I understand your answer correct

If Qlik is formating my data wrong (and I think it does), how can I prevent Qlik from this?

Or do you have a workaround for me?

- Emanuel

[Anonymous]

I had the same problem.

Have a look at this example excel input file, everything in text format:

the table box looks like that, because you have to distinct between text representation and number representation:

the solution is quite simple, just change the text representation of TRBKEY:

num(TRBKEY) as TRBKEY

...

[Anonymous]

Hey Robin,

thanks for your answer, unfortunately I have text values (A,B,C) in my TBRKEY.

But I fixed it with an if:

if(Isnum(TBRKEY),num(TBRKEY), TBRKEY) as TBRKEY_correct,

It is in my opinion not the prettiest solution but it works.

Or do you have any better solutions?

- Emanuel

[Anonymous]

How did you check you data?

I asume you use something like an ODBC driver for connecting QlikVIew with your data base.

It could by the case that your "checking tool" doesn't show leading zeros but the ODBC driver does.

You could try the same ODBC connection in combination with MS Access for example.

[Anonymous]

To avoid this, I used the following tool: http://portableapps.com/apps/development/database_browser_portable

This don*t show the leading zeros.

Thus it seams that the ODBC driver is the problem.

Do you have an idea how to disable this "feature" in the ODBC driver?

[Anonymous]

I think num(TRBKEY) should be sufficient, because for example num('A') = A