Rows deleted during creating datamodel

[qlikhalmar]

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!

[anat]

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 .....

[qlikhalmar]

Hi Anat,

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

[anat]

yes ,we should not delete unused rows.

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

[qlikhalmar]

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.

[anat]

check is any inner join/keep in script?

[marcus_sommer]

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 

[qlikhalmar]

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.....

[marcus_sommer]

1.

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. 

2.

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

[qlikhalmar]

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.