topic Re: Qlik Sense not loading complete data from oracle SQL in QlikView

Qlik Sense not loading complete data from oracle SQL

sundarakumar — Thu, 27 Sep 2018 12:58:13 GMT

Hi Team,

I have a table in orcale DB , when i try to load the data in Qlik sense only partial data is being loaded i.e. out of 200,000 records only 30,000 records are loaded. It happens only with a particular table and it does not throw any errors also.

any idea?

Thanks in advance.

Regards,

Sundar

Re: Qlik Sense not loading complete data from oracle SQL

timpoismans — Thu, 27 Sep 2018 13:09:16 GMT

Are you using the wizard to create the script to retrieve the data?

If not, try it like that to see if there's any influence on the number of rows you retrieve.

Re: Qlik Sense not loading complete data from oracle SQL

sundarakumar — Thu, 27 Sep 2018 13:37:39 GMT

Hi Tim,

Thanks for the response.

I tried with wizard, but i get the same result, missing records!

Any other suggestions?

-Sundar

Re: Qlik Sense not loading complete data from oracle SQL

Miguel_Angel_Baeyens — Thu, 27 Sep 2018 13:54:22 GMT

Check the driver and with the Oracle DBAs. Most of the cases this issue was reported in the Qlik Community was due to limitations on the amount of rows the account can extract, in either the definition of the account or the settings of the client.

Re: Qlik Sense not loading complete data from oracle SQL

sundarakumar — Thu, 27 Sep 2018 13:58:44 GMT

Hi Miguel,

Thanks for the response.

I doubt if this issues would be anything about extract limits. Because this issue happens with only one table. I am able to load millions of records from other tables in the same table.

-Sundar

Re: Qlik Sense not loading complete data from oracle SQL

timpoismans — Thu, 27 Sep 2018 14:05:16 GMT

Perhaps there's a strange symbol in the data that makes the load stop early?

Can't really give you a direct answer, never have encountered this issue, sorry.

Re: Qlik Sense not loading complete data from oracle SQL

sundarakumar — Thu, 27 Sep 2018 14:07:05 GMT

np. Thank you for the help.

Re: Qlik Sense not loading complete data from oracle SQL

tomasz_tru — Thu, 27 Sep 2018 14:37:26 GMT

Does it return always the same number of rows? Is the number of columns distincly bigger than the other tables? Maybe it's timeout?

Re: Qlik Sense not loading complete data from oracle SQL

Miguel_Angel_Baeyens — Thu, 27 Sep 2018 15:19:32 GMT

I don't, security can be set up by table, actually makes sense, dimensional tables have no limits, but fact tables are limited so you have to perform incremental updates.

Re: Qlik Sense not loading complete data from oracle SQL

sundarakumar — Fri, 28 Sep 2018 02:53:45 GMT

Could not load incrementally also. Tired loading one month of data, it breaks again.

Re: Qlik Sense not loading complete data from oracle SQL

sundarakumar — Fri, 28 Sep 2018 02:58:11 GMT

Hi Tom,

Thanks for the response.

Its strange, when i load different different number of columns, it breaks at different record counts. But when i load one column it is completely loading the data!!!

For Example if the columns are Country, city, month and sales and record count is 200000

When i load Country , month and sales it loads 30000 (every time it loads the same number in this combination)

When i load Country , city and sales it loads 90000 (every time it loads the same number in this combination)

when i load only one of the fields all the records are loaded

-Sundar

Re: Qlik Sense not loading complete data from oracle SQL

sundarakumar — Fri, 28 Sep 2018 08:33:38 GMT

Hi All,

I was able to fix this issue with a work around.

I just added the below code in the select statement of SQL in qlik sense load editor.

Table_name:

load

country,

city,

month,

sales;

sql select country,

city,

month,

sales,

rownum as dummy_rownum,

1 as dummy_number,

'1' as dummy_num_string,

'asfdsfdsafdsfs' as dummy_string1,

'abcdefghijklmnop' as dummy_string

from tablename;

This worked for me.

I guess this is more like making a QVD load "non-optimized" in Qlik by adding a where clause or a transformation while loading the data. (refer: optimize and non-optimze load in qvd)

But here may be it is similar thing with a sql load. with a rownum we might have forced the sql to read through all the record.

Note: may be u need to change the dummy_strings lengths if you have strings of longer length.

Happy Learning:)

-Sundar

Re: Qlik Sense not loading complete data from oracle SQL

Asha — Fri, 08 Feb 2019 14:54:33 GMT

Hi Sundar,
I'm facing the same issue, I tried adding the code in Select statement as you mentioned above..but it is still the same..There are 800K records in the View but it fetches only 31k.
Were you able to figure out what caused this issue.?

Thanks,
Asha