Data in cockpit is not the same as data in preview of data set

Question

Hi everyone,

I found a problem and i don't know how to solve it.

I created a dataset making a query to a postgresql and the results on the preview are not the same as the results/data i get in the cockpit, when adding the dataset to a table for example.

The query is quite big so i set a limit of 10k and the dataset is created without problems and all the data is retrieved, but later is not shown correctrly in the cockpit. Other datastes have about +70k rows and in the cockpit everything is correct, so i dont think is a problem with the quantity of data.

If you need to know more about the query is just a right join of two tables of postgresql.

Is the first time that this happened to me so im very confused and don't know how to solve it.

Thanks in advance

Comments

Dear kmorabui,

in order to understand what is happening we need more information.

Can you post a screenshot of your cockpit widget configuration (I guess it's a table widget, isn't?)?

Can you also tell us if there are sortings, order by, aggregations or filters inside cockpit widgets or inside your query?

Regards,

Matteo

---

Hi matmassa,

Here you have the configuration of the table:

And the query:

In the dataset preview i retrieve the 10000 lines and all the data is correct but later, when adding to the cockpit is different and only about 2k lines are shown.

As i said i have many other datasets and this is the first time that happens to me. Maybe is a query problem but i'm not sure.

Thanks a lot

---

I think the query behind widget table result is different than dataset query.

In the table widget you have an order by "Year" and table widget apply aggregations itself using SUM default aggregation for measures, you can see those query into knowageDatasetAudit.log log file.

Let me know if you can see those queries and if it is correct for you.

---

Yes, you were completely right the two queries were different.

I made a change on my postgresql dataset query taking out the order by at the end and later leaving the configuration of the table widget as it is by default. Problem now is that i retrieve around 6k rows so its still not the 10k of the preview.

Maybe this is still happening because of the SUM default aggregation you commented and the two measure columns i have in the table (msrvalue, msrvalue26). Is this possible?

Thanks again matmassa.

Answer 1

Dear kmorabui,

table widget performs aggregations on measures, please check this paragraph (https://knowage-suite.readthedocs.io/en/8.0/functionalities-guide/cockpit/index.html?highlight=aggregation#table-widget).

If you are able to see those queries you should see also those aggregations.

Regards

Matteo