Issue with left Join

[Vikash2024]

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. 

[marcus_sommer]

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

[anderseriksson]

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.

[Vikash2024]

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

[marcus_sommer · Accepted solution]

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;

[anderseriksson]

Use Qlik Help!

[Vikash2024]

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.

[marcus_sommer]

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.

[Vikash2024]

In My case there is one to many relation.

[marcus_sommer]

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.