Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

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!!)

11 Replies
mwoolf
Honored Contributor II

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

You might post your load script.

erich_shiino
Honored Contributor

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

Hi,

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

Hope this helps,

Erich

Not applicable

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

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;

Screen1.JPG

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.

mwoolf
Honored Contributor II

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

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.

Not applicable

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

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?

mwoolf
Honored Contributor II

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

I wish I knew.

Have you tried just the

SQL SELECT *

FROM PUB.DETMOVIN;

without the load?

MVP
MVP

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

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

- Ralf

Not applicable

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

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.

MVP
MVP

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

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

Community Browser