Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Extracting from DB datasource will incorrectly format fields.

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

SQLTable.png

QVD results

QVD.png

1 Solution

Accepted Solutions

Re: Extracting from DB datasource will incorrectly format fields.

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');

8 Replies
mato32188
Contributor III

Re: Extracting from DB datasource will incorrectly format fields.

Hi Jason,

use preceding load

Load ROWWID, text(PARTNUM), PRODNAME

;

SQL SELECT...

       FROM

Martin

Not applicable

Re: Extracting from DB datasource will incorrectly format fields.

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');

QlikError.png

Re: Extracting from DB datasource will incorrectly format fields.

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');

Not applicable

Re: Extracting from DB datasource will incorrectly format fields.

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

Employee
Employee

Re: Extracting from DB datasource will incorrectly format fields.

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.

MVP
MVP

Re: Extracting from DB datasource will incorrectly format fields.

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;

Not applicable

Re: Extracting from DB datasource will incorrectly format fields.

Massimo,

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

Thanks

Jason

Not applicable

Re: Extracting from DB datasource will incorrectly format fields.

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

Community Browser