topic Re: Larger Resultset from View in QlikView

Larger Resultset from View

Mon, 20 Mar 2017 12:49:32 GMT

Hello,

I created a view that has a record count of 3 mio rows.

When loading it to qlik sense it fetches about 11 mio rows.

The used code is quite simple - it's just a SELECT id, date FROM my_view;

I really do not understand this - why does it happen?

any ideas, suggestions?

Re: Larger Resultset from View

sunny_talwar — Mon, 20 Mar 2017 12:51:47 GMT

Are you doing any joins in Qlik Sense?

Re: Larger Resultset from View

Anil_Babu_Samineni — Mon, 20 Mar 2017 12:52:48 GMT

Can you share the script over here

Re: Larger Resultset from View

Mon, 20 Mar 2017 13:33:19 GMT

LOAD ID,
  [Measure_1],
  [Start Date],
  [Month],
  [Year];


[my_table]:
SELECT "ID",
  "Measure_1",
  "Start Date",
  "Month",
  "Year"
FROM "db_mydb"."myview";

Only this table is loaded - the other parts are commented out...

The view contains the logic, I only want to display the results.

Later I will use the ID to join this table with the others.

But the count(*) is wrong, Qlik sense loads far too many rows.

Re: Larger Resultset from View

Mon, 20 Mar 2017 14:17:35 GMT

Interesting phenomenon!

Within the data preview I can now see "Start Dates" from "06.10.1954". Very strange.

I restricted the view to load only data from the current year.

This doesn't make any sense to me right now... What's going on ?

Re: Larger Resultset from View

sunny_talwar — Mon, 20 Mar 2017 14:48:51 GMT

How does Start Date look in your database? Can you post some screenshots?

Re: Larger Resultset from View

Mon, 20 Mar 2017 16:12:04 GMT

Hello!

START_DATE is of data type <DATE>

The view has to be restricted, so I used this attribute within the WHERE-Clause.

No errors when executing in SQL-Developer. Very simple example...but in QS

i get this error.

How and at which place can you restrict the to be loaded data ?

...and which Syntax ? is there a special syntax necessary ?

Does Qlik Sense interpret the view in a different way ?

Please help

Connector reply error: ***

Error: ?

Re: Larger Resultset from View

sunny_talwar — Mon, 20 Mar 2017 16:14:08 GMT

Where did you used the where clause? Select or Load?

Re: Larger Resultset from View

Mon, 20 Mar 2017 16:17:01 GMT

I used the WHERE-clause when defining my view...

Re: Larger Resultset from View

sunny_talwar — Mon, 20 Mar 2017 16:18:22 GMT

Oh the view already has the where clause... then you just did a direct select * from view? should not have error-ed out... not sure what might have caused it... Can you post the screenshot of the error message?

Re: Larger Resultset from View

maxgro — Mon, 20 Mar 2017 20:17:28 GMT

SELECT * from owner.table

should return the same rows in every tool (sense, sqlplus, sql developer, etc....)

Check you're using the same database and the same oracle user