topic Re: Problem in ODBC extraction, it doesn't extracts all the records. in QlikView

Problem in ODBC extraction, it doesn't extracts all the records.

Tue, 11 Oct 2011 18:18:53 GMT

Hi!

I'm using QlikView 10, and I already conected it to a Progress Database using an ODBC, but when I extracted all the records of a table, it only showed me the first 100 records, when the table has over 2,000,000. I already tried the extraction with another program (Crystal Report), and worked fine, showing me all the 2,000,000. It's a Qlikview problem, or it can be anything else?

(Sorry for my poor english grammar!!)

Problem in ODBC extraction, it doesn't extracts all the records.

m_woolf — Tue, 11 Oct 2011 18:23:49 GMT

You might post your load script.

Problem in ODBC extraction, it doesn't extracts all the records.

erichshiino — Tue, 11 Oct 2011 18:40:16 GMT

Hi,

Can you use OLEDB ? Maybe you won't have this problem and it would be faster

Hope this helps,

Erich

Re: Problem in ODBC extraction, it doesn't extracts all the records.

Tue, 11 Oct 2011 18:50:02 GMT

This is my load script (The user and password have been eliminated for security matters):

ODBC CONNECT TO QlikView (UserId is ************, Password is ************);

LOAD "MOV_NUM_CTRL",

"ART_NUM_CTRL",

"DMO_CANT",

"DMO_COSTO",

"DMO_PRECIO",

"DMO_DSCTO1",

"DMO_DEV_SUR",

"ALM_NUM_CTRL",

"TMO_NUM_CTRL",

"DMO_FECHA",

"DMO_COMISION",

"DMO_REN",

"MON_NUM_CTRL",

"DMO_NUM_CTRL",

"DMO_DSCTO2",

"DMO_DSCTO3",

"DMO_COSTOEXT",

"DMO_PRECIOEXT",

"DMO_HORA",

"DMO_CANCELADO",

"DMO_REF4",

"DMO_REF2",

"DMO_REF3",

"DMO_REF5",

"DMO_DETONANTE",

"DMO_REGALO",

"DMO_PADRE";

SQL SELECT *

FROM PUB.DETMOVIN;

As you can see, it only loads 100 lines.

@Erich Shiino: No, I can't use OLE DB, because the version of Progress i'm using is 9.1D, and it does not supports OLE DB.

Re: Problem in ODBC extraction, it doesn't extracts all the records.

m_woolf — Tue, 11 Oct 2011 18:54:31 GMT

What if you try to load the data into Excel using the same ODBC connection?

If you still get just 100 records, that rules out a problem with QlikView.

Re: Problem in ODBC extraction, it doesn't extracts all the records.

Tue, 11 Oct 2011 19:19:11 GMT

I tried it with Excel, and, voila! It loaded almost all the rows (it even sended a message saying "The query exceeded the number of rows allowed", so, it loaded the max of records that Excel can).

It worked in Excel, but not in QlikView. What's the problem then?

Re: Problem in ODBC extraction, it doesn't extracts all the records.

m_woolf — Tue, 11 Oct 2011 19:38:59 GMT

I wish I knew.

Have you tried just the

SQL SELECT *

FROM PUB.DETMOVIN;

without the load?

Re: Problem in ODBC extraction, it doesn't extracts all the records.

rbecher — Wed, 12 Oct 2011 07:44:51 GMT

Have you tried SELECT with the column list (not star)?

- Ralf

Re: Problem in ODBC extraction, it doesn't extracts all the records.

Wed, 12 Oct 2011 17:40:34 GMT

Sorry, but I can´t use the Select sentence without the Load, because a couple of rows in the table have more records than the Qlikview can allow. Every time, when I load those rows, it says:

"Column DMO_REFERENCIA (the row name) in table PUB.DETMOVIN has value exceeding its max length or precision"

So I must use the Preceding Load, and comment those rows. Of course, when I only used the "Select" statement without the Load, it crashed.

Re: Problem in ODBC extraction, it doesn't extracts all the records.

rbecher — Wed, 12 Oct 2011 18:13:10 GMT

Btw. it is also possible to connect via JDBC, maybe this could help..

Problem in ODBC extraction, it doesn't extracts all the records.

Emmanuelle__Bustos — Wed, 01 Feb 2012 17:31:56 GMT

Hello edermoises

Did you solved this issue??

Problem in ODBC extraction, it doesn't extracts all the records.

Mon, 13 Feb 2012 15:57:50 GMT

"Column DMO_REFERENCIA (the row name) in table PUB.DETMOVIN has value exceeding its max length or precision"

This IS your problem! I have seen this with a few of my customers using Progress.

the precending LOAD statement masks the problem and you will get no error (but instead an incomplete record set). I think this should be fixed by Qliktech. I have seen similar problems with other ODBC drivers (not getting an error when using a precending load on your SQL statement, but without the precending LOAD you will see the error).

The problem with progress is that it is possible to store values larger then then precision, ODBC will crash on that.

Ask the Progress administrator to fix the column precision (or the value causing the problem). I don't know how to do that, I am not a Progress DB administrator.

Kind Regards,

Diego Bormann

OneLine Business Intelligence