topic Re: Field Format in QlikView

Field Format

Anonymous — Wed, 01 Feb 2017 13:51:25 GMT

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.

Re: Field Format

swuehl — Wed, 01 Feb 2017 13:56:30 GMT

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.

Re: Field Format

marcus_sommer — Wed, 01 Feb 2017 13:56:46 GMT

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

Re: Field Format

Anonymous — Wed, 01 Feb 2017 18:18:07 GMT

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

Re: Field Format

Anonymous — Wed, 01 Feb 2017 18:29:40 GMT

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

Re: Field Format

Anonymous — Wed, 01 Feb 2017 18:32:29 GMT

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

...

Re: Field Format

Anonymous — Wed, 01 Feb 2017 18:42:19 GMT

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

Re: Field Format

Anonymous — Wed, 01 Feb 2017 18:44:22 GMT

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.

Re: Field Format

Anonymous — Wed, 01 Feb 2017 18:48:09 GMT

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?

Re: Field Format

Anonymous — Wed, 01 Feb 2017 18:48:10 GMT

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

Re: Field Format

Anonymous — Wed, 01 Feb 2017 19:02:41 GMT

Unfortunately not, as you can see in the following screenshot. I removes my characters.

Don't understand why.

Re: Field Format

Anonymous — Wed, 01 Feb 2017 19:03:28 GMT

I have no idea, I would just use num(TRBKEY) , QlikView does the job for you.

Re: Field Format

swuehl — Wed, 01 Feb 2017 21:44:20 GMT

Since the field is called TRBKEY, are there more as 1 table from your DB involved?

Have you checked the format in all tables?

Re: Field Format

rubenmarin — Wed, 01 Feb 2017 22:05:38 GMT

Hi, you can try with.

Alt(TBRKEY, TBRKEY) as TBRKEY_correct,

It's seems a little pretty to me.

or: Alt(num(TBRKEY), TBRKEY) as TBRKEY_correct

Re: Field Format

Anonymous — Thu, 02 Feb 2017 14:24:16 GMT

You were definite wright!

Qlik loads the first value and used the format on the other tables.

The solution:

Before the SQL load I used the Qlik-Cast.

Result:
load *,text(TBRKEY) as myKey; SQL select * from TBPOSP;

And now I always get the correct solution.

Thanks to your and the answer of the other people!!!