topic Re: Issue with left Join in Integration, Extension & APIs

Issue with left Join

Vikash2024 — Wed, 19 Feb 2025 07:52:30 GMT

Suppose I have 2 tables. First Table has 16483 rows and Second table has 21483. When I use left join rows count increased and rows are 89137. How to handle the duplication and records will still same.

Re: Issue with left Join

marcus_sommer — Wed, 19 Feb 2025 08:35:13 GMT

See: Don't join - use Applymap instead - Qlik Community - 1467592

Re: Issue with left Join

Anders_Eriksson — Wed, 19 Feb 2025 08:50:33 GMT

Join merges tables using common fields.
If a row in the left table matches several rows in the right table the rows will be multiplied.
In theory you could end up with 16 483 * 21 483 = 354 104 289 rows (though unlikely).
Like Marcus says, ApplyMap is a safer alternative but could be cumbersome if you want many columns.

Re: Issue with left Join

Vikash2024 — Wed, 19 Feb 2025 10:35:44 GMT

Can you provide me the example of ApplyMap() instead of using Left Join?

Re: Issue with left Join

marcus_sommer — Wed, 19 Feb 2025 10:48:54 GMT

There is one included within the above provided link.

I suggest to start with such simple approach by using one field as lookup and one as return - to comprehend the logic. If it's understood and be working you may extend it if necessary by string-concatenating n lookup- und n return-fields, like:

m: mapping load F1 & '|' & F2, F3 & '|' & F4 from X;

t: load *, subfield(applymap('m', F1 & '|' & F2, 'myDefault'), '|', 1) as F3 from Y;

Re: Issue with left Join

Anders_Eriksson — Wed, 19 Feb 2025 14:45:59 GMT

Use Qlik Help!

Re: Issue with left Join

Vikash2024 — Wed, 19 Feb 2025 17:55:51 GMT

ApplyMap() can only map single values based on a key. Suppose that A customer may have multiple invoices, and you want to pull all invoices for a given customer With ApplyMap() It would only retrieve the first match found for the customer ID. What about the rest of invoices.

Re: Issue with left Join

marcus_sommer — Thu, 20 Feb 2025 06:39:39 GMT

The fact that applymap() fetched always only the first match isn't a restriction else a quite powerful feature because it enables for example the possibility to use vertically nested hierarchy mappings.

And by all the "classical" use-cases you have only one key-value. If there is more as a single key-value it would mean that's the wrong key (more and/or other fields has to be combined) and/or the direction of the usage is not suitable.

For your scenario it means that you should add customer-information to the invoices and not the revers way - respectively you may skip this measurement entirely. Because invoices are facts and customers are dimensions and within a normal star-scheme data-model it won't be necessary to have (granular) invoice-information within the customers.

Re: Issue with left Join

Vikash2024 — Thu, 20 Feb 2025 08:08:04 GMT

In My case there is one to many relation.

Re: Issue with left Join

marcus_sommer — Thu, 20 Feb 2025 08:28:08 GMT

I think it's logically the wrong direction and not helpful or necessary.

The facts must have some kind of a Customer-ID and with this and an associated Customer-Table you could show all usual sum() and count() views in regard to the customers.

If there are further requirements like creating information of the order of the invoices and there date-offset to each other and/or the first/latest one - they belong to the fact-table or might be from there derived within an separate extra table. If any kind of customer-classification in regard to the frequency or amount of invoices and so on is wanted - they have to be at first aggregated from the facts and could be then added to the customers.

Re: Issue with left Join

Vikash2024 — Thu, 20 Feb 2025 10:07:28 GMT

Ok thanks for the help. I will try again ApplyMap().