topic Re: Extracting from DB datasource will incorrectly format fields. in QlikView

Extracting from DB datasource will incorrectly format fields.

Mon, 08 Dec 2014 17:08:46 GMT

Currently I am extracting the Item Master table via SQL from our Oracle DB. The PN field when extracted is being stored incorrectly. An example would be that we have PNs 00001, 01 and 1.0000. In the source tables they all show that way. When I extract from QlikView, it is storing all 3 PNs as 00001. This is happening with several "like" PNs and isn't following any logic. I could understand that if QLik thought the Part Number field was a number it would read every field as "1". But it's like QlikView is doing a data cleanse and assuming that these all should be 00001. Has anyone seen this before? This doesn't seem to be limited to only one data source. I have seen this extracting from the Oracle OBIEE warehouse, Oracle EBS DB and Microsoft SQL server.

Thank
Jason

SQL Results via SQL Editor

QVD results

Re: Extracting from DB datasource will incorrectly format fields.

mato32188 — Mon, 08 Dec 2014 17:16:15 GMT

Hi Jason,

use preceding load

Load ROWWID, text(PARTNUM), PRODNAME

;

SQL SELECT...

FROM

Martin

Re: Extracting from DB datasource will incorrectly format fields.

Mon, 08 Dec 2014 18:01:14 GMT

Martin,

This is the script that I tried. I received an error that appeared to not take.

Thanks

Jason

LOAD

rowwid, text(partnum),prodname;

select
row_wid,part_num,prod_name

from w_product_d where row_wid IN ('49095492','49193572','49055983');

Re: Extracting from DB datasource will incorrectly format fields.

Anonymous — Mon, 08 Dec 2014 18:09:42 GMT

On the second line put an underscore in row_wid

LOAD

row_wid, text(partnum),prodname;

select
row_wid,part_num,prod_name

from w_product_d where row_wid IN ('49095492','49193572','49055983');

Re: Extracting from DB datasource will incorrectly format fields.

Mon, 08 Dec 2014 18:37:18 GMT

Bill,

Thanks, I tried that too and got the same result where it couldn't find the field "row_wid".

LOAD

row_wid, text(partnum),prodname;
select
row_wid,part_num,prod_name
from w_product_d where row_wid IN ('49095492','49193572','49055983');

Thanks

Jason

Re: Extracting from DB datasource will incorrectly format fields.

JonnyPoole — Mon, 08 Dec 2014 18:43:59 GMT

Jason did the QV wizard create this SQL for you or is it a copy/paste of SQL ?

If you haven't already tried it, use the ODBC connection in the script to select this table and one field (row_wid) to see how Qlik autogenerates the SQL and see if syntax differences exist.

Re: Extracting from DB datasource will incorrectly format fields.

maxgro — Mon, 08 Dec 2014 18:50:23 GMT

pay attention to case with oracle

load

ROW_WID,

...

sql select

row_wid,

....

example

OLEDB CONNECT32 TO [Provider=OraOLEDB.Oracle.1;Password=xxxxxxxxxxx;Persist Security Info=True;User ID=xxxxxxx;Data Source=xxxxx;Extended Properties=""];

load text(FIELD1) as FIELD1;

select field1 from a;

Re: Extracting from DB datasource will incorrectly format fields.

Mon, 08 Dec 2014 19:10:18 GMT

Massimo,

That was it. The capitalization was incorrect and causing the issues.

Thanks

Jason

Re: Extracting from DB datasource will incorrectly format fields.

Sun, 22 Mar 2015 14:51:02 GMT

http://dbmanagement.info/Tutorials/Oracle_EBS.htm

Oracle EBS- Video Training Tutorial DVDs (2 DVDs)

More than 8 GB of Video files Sale Price 19.97