topic Re: Db2 format problems in QlikView

Db2 format problems

jubarrosor — Mon, 06 Oct 2014 14:38:12 GMT

Hi,

I have an issue when I read data from a db2.

I'm using an odbc connection to db2:

and I'm reading datas using a "select *..." without any transformation:

The problem is:

There is a data changed, his code has been modified in one character: xxxxxxEx for xxxxxxxDx so there's two register with the same code (because the code is PK in the source table).

Could you help me? 😞

Thak you!

Best Regards!

Re: Db2 format problems

jubarrosor — Tue, 07 Oct 2014 07:34:43 GMT

The codification of data in source is EBCDIC

Re: Db2 format problems

ashfaq_haseeb — Tue, 07 Oct 2014 07:42:55 GMT

Hi,

Can you explain a bit more.

What are you trying to achieve here?

Regards

ASHFAQ

Re: Db2 format problems

tresB — Tue, 07 Oct 2014 07:44:08 GMT

Not quite clear. Is it specific to DB2? If not, please explain the data (tables, fields, records) part a bit more with example.

Re: Db2 format problems

jubarrosor — Tue, 07 Oct 2014 09:42:58 GMT

Hello,

I'm trying read data froma a DB2 database and I'm using an ODBC connection for it.

The problem is that there's one data that has been modified.

In the database, there are two different registers with his own code:

but Qlikview is reading both registers with the same code:

I only read data and I didn't do any modification of them, but they have been changed. (The value 3E3 is reading like 3D3.)

Thank you and sorry for my english!

Re: Db2 format problems

ashfaq_haseeb — Tue, 07 Oct 2014 09:54:18 GMT

Hi,

After modifying at DB did you reload once in Qlik?

are you using same source table in Qlik?

Regards

ASHFAQ

Re: Db2 format problems

jubarrosor — Tue, 07 Oct 2014 10:00:48 GMT

Hi ashfaq_haseeb,

the source tabla is the same.

I'm not modifying the data at DB, the data have been modifying on the load

Situation:

There are some data in DB, I'm reading data but the data in source are different of the data in target.

Thanks!

Best Regard!

Re: Db2 format problems

ashfaq_haseeb — Tue, 07 Oct 2014 10:12:09 GMT

Can you post your script here, that you use for modification.

how many tier you use to reload data?

did you check at all available stages?

Regards

ASHFAQ

Re: Db2 format problems

deepakqlikview_123 — Tue, 07 Oct 2014 10:16:31 GMT

It seems data has not been changed in target so you are getting same result.

Thanks

Re: Db2 format problems

jubarrosor — Tue, 07 Oct 2014 10:17:23 GMT

The script is only a LOAD (you can see it at the first post):

IS_KP_QRS_DET:

LOAD

SQL SELECT *

FROM IS_KP_QRS_DET;

store IS_KP_QRS_DET into IS_KP_QRS_DET.qvd (qvd);

Best Regards!

Re: Db2 format problems

ashfaq_haseeb — Tue, 07 Oct 2014 10:20:44 GMT

I think we miss something

Contact me on Skype

myskypeID : ashfaqhaseeb

Re: Db2 format problems

jubarrosor — Tue, 07 Oct 2014 10:22:03 GMT

Hi deepak,

The data are changed:

in source there are two codes in column IDEQRS:

130828543D3

130828543E3

in target:

both register are the same value 130828543D3 in column IDEQRS.

Best Regards!

Re: Db2 format problems

jubarrosor — Tue, 07 Oct 2014 12:49:50 GMT

Hello,

I don't know the problem yet but i have a solution.

If I concatenate a '$' to the column IDEQRS all codes are reading well.

I think that the odbc connection reads the code 130828543E3 like a number (but the column is alfanumeric) and this is the reason because the results are wrong (if I concatenate '$' the column are readed like an alfanumeric).

Is it a bug?

Thank you and sorry for my english!

Best Regards!

PS: Thank you very much Ashfaq! but I haven't skype in my job.

Re: Db2 format problems

jonathandienst — Tue, 07 Oct 2014 13:01:49 GMT

No, its not a bug. Here's what is happening:

Both 1234E5 and 1234D5 (for example) are valid floating point number forms (E is single precision, D is double precision). QV considers them to be equal because they are loaded as numbers
QV uses the first row to determine the display format. If the row contains 1234D5 it will be formatted as such. When QV reads the second row (containing 1234E5), it applies the same format, hence 1234D5.

To get around this, you must tell QV to use a text load. This what your workaround is in effect doing, but you can do it simply with

LOAD Text(IDEQRS) As IDEQRS,

....

;

SQL SELECT IDEQRS, .....

HTH

Jonathan

Re: Db2 format problems

jubarrosor — Tue, 07 Oct 2014 13:09:36 GMT

Thank you Jonathan!!!!!