Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
deerio
Contributor
Contributor

Inner Keep - Importance of sorting

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.

deerio_0-1700246060747.png

 

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

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

deerio_1-1700246060749.png

 

Tables are so composed:

Table 1:

deerio_2-1700246060749.png

 

Table 2:

deerio_3-1700246060750.png

 

 

Table 3:

deerio_4-1700246060751.png

 

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

deerio_5-1700246060752.png

 

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:

deerio_6-1700246060753.png

 

Table 2:

 

deerio_7-1700246060753.png

 

Table 3:

deerio_8-1700246060754.png

 

 

 

 

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.

Labels (2)
3 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Partner - Contributor
Partner - Contributor

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.