Inner Keep - Importance of sorting

[deerio]

HI,

I would like to have some clarification on how inner keeps works within Qlik Sense and how the order of the tables matters. To be precise I need a data model that loads me 5 tables related via keep. The result I want to obtain is 5 separate tables that only take into account the rows they have in common derived from keep.

As a simplification of the model, I propose a dataset containing 3 tables, shown in the figure.

I have 3 tables composed with the following fields, as in the figure.

1^st case, order Table 1, Table 2, Table 3.

Tables are so composed:

Table 1:

Table 2:

Table 3:

2nd case, order Table 1, Table 2, Table 3.

I have a different composition of the tables, in particular, there is a difference in table 3, where an extra record is shown:

Table 1:

Table 2:

Table 3:

What we need to understand is why reversing the loading order, despite specifying where to perform the Inner Keep, qlik gives different results.

I attach the excel file with the original data and the qvd with the data already loaded.

Thank you.

[rwunderlich]

In your second case, ID2=25 is kept because that value exists in Table1 at the time you execute Inner Keep from Table3. 

In your first case, ID2=25 has been filtered out by the time you execute the Inner Keep from Table3. 

-Rob 

[FedericaP]

yes you are right! but he would like the 3 tables to have only the rows with the keys in common. Keep doesn't seem reliable for doing this because it doesn't consider the script as a whole but runs it sequentially! Unfortunately I don't have a solution to this problem...

[marcus_sommer]

I think you will need some extra measures and/or extra load-steps for the intended results. It's quite rarely that I use keep else in nearly all filter/match-scenarios I apply exists() as where-clauses or as flags within the loads. And here it's not seldom that the load-order and/or the field-names/values and/or the field-combinations aren't suitable for the intended job.

Quite simple measures to overcome such tasks is to double and/or to combine the relevant fields with another field-name and/or to rename existing ones and afterwards renaming them again or dropping the fields. Especially by larger data-sets I do create appropriate exists-tables in beforehand of the main-loads. The earlier such things are considered in the load-chain the simpler are such measures. I think most of these approaches would be applicable with keep, too.

Beside of a direct filtering you may also create direct flags with exists()and/or adding the second/third ID  from the n sources as n parallel fields to the records and within a following resident-load the final evaluation of the valide values/records is applied. By using mapping-approaches you may simplify such a logic because they could be nested in multiple ways.