topic Re: Clarification on Left Keep vs. Left Join in Qlik in App Development

Clarification on Left Keep vs. Left Join in Qlik

pallavi_96 — Fri, 28 Jun 2024 08:16:28 GMT

Hello Qlik Community,

I'm currently working with Qlik Sense and I'm trying to understand the difference between "left keep" and "left join" operations in Qlik's scripting language. I've encountered a scenario where the results from these operations differ, and I'm seeking clarification on how they function differently.

Scenario:

I have two tables, let's call them Table A and Table B, with the following structures:

Table A:

A:
Load * Inline
[

F1,F2
A, 1
B, 2
C, 1
];

Table B:

Load * Inline

[

F1, F3
A, 2
A, 3
A, 4
B, 1
];

Output:

Join=>

Keep=>

Analysis:

When I perform a left join on Id, I get a 3 Id count for A.
However, when I use "left keep" instead of "left join," I notice a different count i.e. 1 Id count for A.
Also when I see in data manager for Left keep it shows 1:M relationship.

Specific Questions:

What is the precise difference between "left join" and "left keep" in Qlik?
Why does the count of rows for Table A differ between these operations in my scenario?
How does "left keep" handle multiple matches in Table B compared to "left join"?

I'm looking for insights into how these operations are implemented in Qlik Sense specifically, and any examples or explanations that could help clarify their behavior would be greatly appreciated.

Thank you in advance for your assistance!

Re: Clarification on Left Keep vs. Left Join in Qlik

marcus_sommer — Fri, 28 Jun 2024 10:54:43 GMT

A join merged tables while a keep remained with separately tables. Of course the relationship between the tables is important and usually non of the shown scenarios is really relevant respectively not comparable to each other.

A 1:n join which duplicates records is rather seldom intentionally used and if it would exclude keep as an alternatively approach. The common aim of keep isn't to merge else to filter tables and here mainly to filter a dimension-table against a fact-table, for example to exclude older products/dates/staff ... which aren't included within the sales of the last n periods.

To conclude the join/keep-logic is quite similar to each other but the use-cases are different.

Beside of this, within the most scenarios - especially in well build star-scheme data-models - the calculation against key-fields will be working and returning the expected results but in other data-models and/or having 1:n or n:m relations and/or having missing keys and/or NULL in them it may not working as expected. As far as there are any doubts - don't calculate with them else use normal fields (maybe just a duplicated field).

Re: Clarification on Left Keep vs. Left Join in Qlik

pallavi_96 — Wed, 03 Jul 2024 05:56:11 GMT

Hi @marcus_sommer

Thank you for addressing my query.