Qlik Data Load Editor: Join 2 tables checking if 2 fields are the same to avoid duplicates

[marceloromero]

Hello Community,

I have 2 really big tables that don't have a unique id (they are document tables with versioning).

The first table is the list of documents that have been created/updated, let's call it 'New table'.

New table:

ID, Version, LastVersion, RecordDate, LastUpdated
1, 4, 'N', '01/01/2019', '01/01/2024'
3, 1, 'Y', '01/01/2024', '01/01/2024'
1, 5, 'N', '01/01/2019', '01/01/2024'
1, 6, 'Y', '01/01/2019', '30/01/2024'

And the second table is the big old list containing all the documents.

Old table:

ID, Version, LastVersion, RecordDate, LastUpdated
1, 1, 'N', '01/01/2019', '01/01/2020'
1, 2, 'N', '01/01/2019', '01/01/2021'
1, 3, 'N', '01/01/2019', '01/01/2022'
1, 4, 'Y', '01/01/2019', '01/01/2023'
2, 1, 'Y', '01/01/2023', '30/01/2023'

As you can see in this example, when you compare both tables you can see that 1 document has been updated and 3 documents have been created.

What I want to do is to load the New table and then add all the rows that are missing avoiding duplicates by checking on the combination of ID+Version. The problem is that I don't want to Load the second table because it is awfully huge, so maybe I need to do a Join with some kind of Where at the bottom, I'm really lost.

The expected result is the following:

ID, Version, LastVersion, RecordDate, LastUpdated
1, 1, 'N', '01/01/2019', '01/01/2020'
1, 2, 'N', '01/01/2019', '01/01/2021'
1, 3, 'N', '01/01/2019', '01/01/2022'
1, 4, 'N', '01/01/2019', '01/01/2024'
2, 1, 'Y', '01/01/2023', '30/01/2023'
3, 1, 'Y', '01/01/2024', '01/01/2024'
1, 5, 'N', '01/01/2019', '01/01/2024'
1, 6, 'Y', '01/01/2019', '30/01/2024'

What could I do?

Thanks for your time 😉

- Marcelo

[mpc · Accepted solution]

Hi, 

I don't know if this code is the more efficient, but it's work:

New:
Load
AutoNumber(ID & Version & LastUpdated) as Key,
*
Inline [
ID, Version, LastVersion, RecordDate, LastUpdated
1, 4, 'N', '01/01/2019', '01/01/2024'
3, 1, 'Y', '01/01/2024', '01/01/2024'
1, 5, 'N', '01/01/2019', '01/01/2024'
1, 6, 'Y', '01/01/2019', '30/01/2024'
];

Join
Old:
Load
AutoNumber(ID & Version & LastUpdated) as Key,
*
Inline [
ID, Version, LastVersion, RecordDate, LastUpdated
1, 1, 'N', '01/01/2019', '01/01/2020'
1, 2, 'N', '01/01/2019', '01/01/2021'
1, 3, 'N', '01/01/2019', '01/01/2022'
1, 4, 'Y', '01/01/2019', '01/01/2023'
2, 1, 'Y', '01/01/2023', '30/01/2023'
]
Where Not(Exists(Key, AutoNumber(ID & Version & LastUpdated)));

Please don't pay attention to date format, quick copy / paste

Regards, 

[marceloromero]

Yes! That's what I was looking for. It works perfectly.

My final script looks like this:

New:

Load

AutoNumber(ID & '|' & Version) as Key,

ID,

    Version,

    LastVersion,

    Date(RecordDate) as RecordDate,

    Date(LastUpdated) as LastUpdated

Inline

[

ID, Version, LastVersion, RecordDate, LastUpdated

1, 4, 'N', '01/01/2019', '01/01/2024'

2, 1, 'Y', '01/01/2023', '30/01/2023'

3, 1, 'Y', '01/01/2024', '01/01/2024'

1, 5, 'N', '01/01/2019', '01/01/2024'

1, 6, 'Y', '01/01/2019', '30/01/2024'

];

Join(New)

Load

AutoNumber(ID & '|' & Version) as Key,

ID,

    Version,

    LastVersion,

    Date(RecordDate) as RecordDate,

    Date(LastUpdated) as LastUpdated

Inline

[

ID, Version, LastVersion, RecordDate, LastUpdated

1, 1, 'N', '01/01/2019', '01/01/2020'

1, 2, 'N', '01/01/2019', '01/01/2021'

1, 3, 'N', '01/01/2019', '01/01/2022'

1, 4, 'Y', '01/01/2019', '01/01/2023'

2, 1, 'Y', '01/01/2023', '30/01/2023'

]

Where Not(Exists(Key, AutoNumber(ID & '|' & Version)));

Drop field Key;

Thanks a lot 😉