Getting duplicate records after using Joins for three different tables

pranaview — Sat, 16 Nov 2024 03:05:37 GMT

Hi All,

Requirement: I have to match the OrderID from the Table 1 in the Table 2 and then for the respective AssignmentID in the Table 2, I need to find a match in the Table 3 to complete the connection between the three tables to get the Post Date and Creation Date for each OrderID which i need for the calculations. Now I tried to apply Left Join between Table 1 and Table 2 and then again a Left Join between the resulting table and the Table 3. This works for unique values in the key fields but in real time, it is fairly common to have multiple records for the same OrderID with different Post Dates, DocNumber etc and similarly multiple records for the same AssignmentID with different Creation Date/DeliveryNumber, you get the gist of it.

For Ex: If there are two records for the same OrderID in Table 1 and have two records in the Table 3 for the respective AssignmentID, then after applying the Joins, I will have 4 records in the resulting table rather than just 2 i.e. a duplicate for each record. What i want is to have both Creation Date and Post Date in my final table for each record in the Table 1 without any duplicates. I couldn't use ApplyMap() because of the duplicates in the Key fields(or is there a workaround?)

Below is a simple example of how the data looks and how it is connected.Please help me out here to get the desired result as without getting this done, there's no moving forward. Let me know if the problem is not clear enough

Table 1

OrderID Post Date DocNumnber ....More Columns

1234 06/23/19 98273445

1235 06/23/19 98273450

1235 06/24/19 98273493

Table 2

OrderID AssignmentID ....More Columns

1234 873

1235 874

Table 3

AssignmentID Creation Date DeliveryNumber

873 06/23/19 98749335

874 06/23/19 98749337

874 06/25/19 98749342

Thanks!

Pranav

Re: Getting duplicate records after using Joins for three different tables

pianoman15 — Thu, 04 Jul 2019 15:43:26 GMT

I think that if you put the same name to the fields qlikview will do the match for you.

Re: Getting duplicate records after using Joins for three different tables

pranaview — Fri, 05 Jul 2019 08:56:31 GMT

Yeah it will, but all three tables will remain separate which means Post and Creation Dates would be in separate tables and i won't be able to do calculations using them for each record.

Also it doesn't solve the 4 records instead of 2 problem.

topic Re: Getting duplicate records after using Joins for three different tables in QlikView

Getting duplicate records after using Joins for three different tables

Re: Getting duplicate records after using Joins for three different tables

Re: Getting duplicate records after using Joins for three different tables