Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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,
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,
Yes! That's what I was looking for. It works perfectly.
My final script looks like this: