Pitfalls of using joins

Joins are simple to understand and use, but there is one hidden pitfall that can create lots of issues in your output data. Until now, we have seen simple data that had a one-to-one relationship between the values of the joining field, but in some scenarios, you may encounter multiple relationships between data. In such a case, your output data may get duplicated and the overall result may turn out wrong.

Let's understand this using the same example we used in the preceding sections. The only change will be that we add the last record in the Customer table:

You can see in the preceding table that we added a new record, which makes a duplicate entry for the primary key; that is, CustomerID, with a value of 3.

When we use this table along with any join condition, it will create two records in the resulting table, because the record 3 has a duplicate value. Let's assume that we use left join the Customer table to the Order table. The resulting output would look like the following screenshot:

You can see in the preceding screenshot that order number 102 has appeared twice, one with the customer name as Sam and the other as Vinay. You can also observe that OrderValue also got duplicated due to this new record. So, when you calculate on OrderValue from this resulting table, you will get the wrong value.

Thus, whenever you use any joins in Qlik Sense, you must make sure that the joining table has distinct records to get the correct values after joining.