Error loading data (Field id not found)

[Clem]

Hello,

I have an error loading data that I cannot resolve. And despite my research I can't find where the problem comes from (I'm new to Qlik)

Here is my script:

[ssi_failure]:
LOAD id;
Select id FROM "public"."ssi_failure";
Store [ssi_failure] into [lib://CLIENTS_QVD_FILES (fro1vhlqlik1_administrateur)/$(ClientFloderName)/SI/ssi_failure_Manut.qvd] (qvd); 
Drop Table [ssi_failure];

And when I start the data loading, it tells me that it is connected (to the database) then it loads without stopping and without doing anything. When I give up loading the data, I get this error:

Except that the id field is in the database. I think the problem is that there are too many rows in the database (660,000). Because when I select 70 lines the data loading works (60 lines max, after that gives me the same error):

[ssi_failure]:
LOAD id;
Select id FROM "public"."ssi_failure" FETCH FIRST 60 ROW ONLY;
Store [ssi_failure] into [lib://CLIENTS_QVD_FILES (fro1vhlqlik1_administrateur)/$(ClientFloderName)/SI/ssi_failure_Manut.qvd] (qvd); 
Drop Table [ssi_failure];

For information the database uses postgreSQL as DBMS.

So I wanted to know if you have a solution to make the first script work.

Thanks in advance !

[Clem]

Hi @NadiaB ,

Yes I tested and it comes out the desired result (but takes a little while)

[NadiaB]

Hi @Clem

Are you saying that the query in QS shows an error in seconds, while the actual query in PGAdmin takes a long time to complete? How long does it take for the query in PGAdmin to complete the execution?

Can you limit the amount of rows to retrieve? Maybe at a "limit 10" statement to your query to see if it completes in QS without error?
 

Thank you !

[Clem]

Hi @NadiaB 

The request takes approximately 1 minute in PGAdmin to show lines (600 000)

I have already tried to select just a few lines in qlik and it works up to 70 lines and that's the problem, I want to select more lines

[NadiaB]

Hi @Clem

Thank you for the provided information, by any chance could you confirm if you are using Qlik ODBC Package, are you using OLEDB connection, ODBC, ODBC DSN? If using ODBC DSN could you indicate what driver and version are you using?

Thank you!

[Clem]

Hi @NadiaB 

Im sorry im new to Qlik, how i know what i use?

[Or]

What you're describing is likely attributable to an issue with the underlying query resulting in a failure and as a result, no field for the preceding load to look at. What happens if you remove the Load id;  and let the select query run on its own?

[Clem]

Hello @Or 

thank you for your reply

When I only do SELECT it does the same , when I select 60 lines max it works and after 60 lines it loads without stopping. On the other hand, when I abandon the loading of the data it does not give me any more errors.

[marcus_sommer]

What happens if you use select * ... or some other fields like any date-field from it and also without applying a preceeding load. Also you may try something like:

select len(field) as FieldLength from ...

Further try to load anything from other tables of this database. Another check might go to monitor after which time the errors occur.

The aim behind these measures is to exclude possible causes one after another. If it worked with other fields it indicates that the driver couldn't handle the id field. If all field-combinations from this table didn't work but from other tables it could mean that the query breaks if it tries to access the first locked record. Both causes might be fixed with a different driver and/or adjusted configurations from the driver and/or the database.

If every of these attempts failed each time after n seconds/minutes it indicates any timeout - from the driver or the database as well as any part of your network maybe any proxies, load-balancers ...

- Marcus

[Or]

Is this a view or a table? If it's a table, can you load the first ~70 lines in PGAdmin with the same query and then try and figure out what's different about the lines 61-70 and might be causing the issue?

If it's a view, I would suggest trying to pull the view's SQL and running that from Qlik rather than referencing the view. If that still doesn't work, you can remove parts of the query and hopefully isolate the section(s) causing issues.

[Clem]

Hi @marcus_sommer 

thank you for your reply

select * is worse i can select just 10 rows before there is the infinite data loading problem. For the other fields I have the same problem.

I search its LENGTH in postgreSQL and unfortunately that doesn't change anything i can't select more than 50 rows

When I select in other tables its the same or even worse there are some where even 1 line doesn't work.

is it possible to describe more what you were talking about if each of these attempts fails?