Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Field Format

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!!!

TBPOSP_Mit.png

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!!!

TBPOSP_Ohne.png

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.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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.

View solution in original post

14 Replies
swuehl
MVP
MVP

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
Not applicable
Author

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:

TBPOSP_Ohne_Neu.png

2. with the where case:

TBPOSP_Mit_Neu.png


Any other ideas?

- Emanuel

Anonymous
Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

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