topic Re: Rows deleted during creating datamodel in App Development

Rows deleted during creating datamodel

qlikhalmar — Thu, 14 Apr 2022 07:42:27 GMT

Hi All,

My customer has an old QS app (not my work;) with, among other tables, these 3 tables.

Two big facttables (Offers and Orders) with a lot of common fields
One dimension table with customers

While building the datamodel, after the script reload, QS:

Create a big syn-table
Delete specific rows of the dimension table. The rows that are deleted are the rows with unused key-values in both facttables. In our case the rows of customeres who does not have any offers or orders, but we still want to see those customers.

Is this a bug? Is there a way, beside get rid of the syn table, to fix this?

Thanks!

Re: Rows deleted during creating datamodel

anat — Thu, 14 Apr 2022 08:09:03 GMT

if possible try to avoid Syn-table and join Offers and Orders based on the key field,try to rename common fields with diffrent name like sales_Offers ,Sales_Orders .....

Re: Rows deleted during creating datamodel

qlikhalmar — Thu, 14 Apr 2022 08:12:19 GMT

Hi Anat,

I know that it is better to avoid Syn-tables. But deleting unused rows is strange...don't you think?

Re: Rows deleted during creating datamodel

anat — Thu, 14 Apr 2022 08:19:28 GMT

yes ,we should not delete unused rows.

may be check with your BA ,what was the requirement to delete those rows?

Re: Rows deleted during creating datamodel

qlikhalmar — Thu, 14 Apr 2022 08:25:15 GMT

No, Qlik Sense delete those rows while creating the datamodel and the Syn-tables.

I droped the table just at the end of the reload and compare it with the table after the datamodel is created. Thats why I know that only customers with facts (offers or orders) were kept in the customers table.

Re: Rows deleted during creating datamodel

anat — Thu, 14 Apr 2022 08:48:51 GMT

check is any inner join/keep in script?

Re: Rows deleted during creating datamodel

marcus_sommer — Thu, 14 Apr 2022 13:28:30 GMT

If you don't set any where clauses to the loading or applying any join-statements no records will be deleted. This happens only with an explicit statement.

What you had observed are the effects of missing keys. Depending on from which side you look on the data you will always have records which won't be visible if there were missing keys.

Of course there are ways to handle such scenarios, for example:

checking all tables against each other and generating all missing keys
creating a link-table between those tables
concatenating these tables

The last one with just concatenating these tables is the most easiest one - no trouble with missing keys or synthetic keys to each other or to further dimension-tables. Maybe you need some renaming to unify the field-names and adding an extra source-field to be able to differentiate between them. But that's all. Simple!

- Marcus

Re: Rows deleted during creating datamodel

qlikhalmar — Thu, 14 Apr 2022 14:19:38 GMT

Hi Marcus,

Thanks for your reply! I follow your points, but there are two things still strange:

What you had observed are the effects of missing keys. Depending on from which side you look on the data you will always have records which won't be visible if there were missing keys.

1. But when I don't make any selection in the dashboard, I must see all the values from a table at all times.

2. When I check the no of records of a table in the last line of my loadscript ('10498', see below), that no of records has to be the same as the no of records in the data model viewer (3179).

What i do know is that 3179 the number of values is that exists in the facttable, but that is not what I scripted and what I expect from QS.....

Re: Rows deleted during creating datamodel

marcus_sommer — Thu, 14 Apr 2022 15:12:42 GMT

In general yes, but it depends on the object respectively the combination of used fields and expressions if you could see all values respectively within the expected association to each other. If I have issues with unclear data-sets and their relation I add a respectively several recno() and rowno() to the relevant loads and pull them afterwards with other essential fields into a table-box. RecNo/RowNo make the records unique - within the tables and also in the table-box - which is important to display also redundant records respectively field-combinations. Further they will show clearly which records were loaded and which might be missing and with it it's usually not too difficult to find the reasons (any filters, bad data-quality, locked records if the missing records are randomly). Also displayed is the relation-ship respectively the association between the field-values.

I'm not very familiar with Sense and could therefore not exactly judge what you see there - means if the 3179 might be distinct values. Beside this it could be possible to look on the wrong tables/fields - depending on the load-order, the data-structure on the loads, renaming statements and probably some more reasons it may happens.

Another cause by missing records could be a section access which applied any data-reduction based on the authorization-table. If it's enabled I suggest to disable it for this kind of trouble-shooting.

To repeat my above statement without any explicit statement or measures all records must be there. I never noticed any case without it.

- Marcus

Re: Rows deleted during creating datamodel

qlikhalmar — Thu, 21 Apr 2022 13:53:46 GMT

The problem is still there.

I run the script with the key field to the facttable and my table is reduced during making the datamodel.

I run the script and change the name of the key field and my table is filled with all the records.

And there is no Section Access involved in this case.

So it is a strange behavior that i cannot explane.

Re: Rows deleted during creating datamodel

marcus_sommer — Thu, 21 Apr 2022 14:39:17 GMT

I'm further quite sure that there is no reducing of the data-set during the creation of the data-model. All records which were be loaded at this state will be there because there is no way on which they could be vanish.

Therefore please read my answers from above again - carefully.

In the end you may decide to design the data-model differently but just to check the that all loaded records are there concatenate all fact-tables into a single table and adding recno + rowno + an extra manual source-field. The dimension-tables are not necessary at this point. And now create a table-box within the UI and pull recno + rowno + Source and a few other relevant fields into it - and this check against your raw-data.

- Marcus

Re: Rows deleted during creating datamodel

Thos0036 — Fri, 22 Nov 2024 17:05:50 GMT

I just experienced this same issue and it had not been happening before. Someone suggested a data type issue or data changes on the back-end, but that doesn't really add up for me. Did you ever figure it out @qlikhalmar ?

Re: Rows deleted during creating datamodel

marcus_sommer — Sun, 24 Nov 2024 10:34:33 GMT

Just go carefully through all my above mentioned hints. Without explicit statements no data will be removed and with recno/rowno it's simple to track all data.