Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
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
bill_markham
Champion III
Champion III

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

View solution in original post

8 Replies
mato32188
Specialist
Specialist

Hi Jason,

use preceding load

Load ROWWID, text(PARTNUM), PRODNAME

;

SQL SELECT...

       FROM

Martin

ECG line chart is the most important visualization in your life.
Not applicable
Author

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

bill_markham
Champion III
Champion III

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
Author

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

JonnyPoole
Employee
Employee

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.

maxgro
MVP
MVP

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
Author

Massimo,

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

Thanks

Jason

Not applicable
Author

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