Load if distinct???? Combining Data Files, but not loading duplicates

[JoeCharter]

I have 2 data files that contain the same fields.  One file is from last year and the other file is from this year.  

The files keep track of estimate information and each entry is timestamped with "Modified Date".  If an estimate was only edited last year then it won't be in this year's file.  If the estimate was only edited this year then it won't be in last year's file.  But there are many instances where the estimate spanned both years and is included in both files.  I only want to load the data with the most recent Modified Date.

The other challenge is that each estimate loads multiple rows of data.  They all have the same Estimate ID, but show different details about the estimate.  I only want to keep the most current rows for each estimate.

Does something like the bold text below exist?

Table 1:

LOAD Estimate ID, Estimate Name, Modified Date, ....other estimate info... FROM [lib://.......this year]

Table 2:

LOAD (if Estimate ID is not already in Table 1) Estimate ID, Estimate Name, Modified Date, ....other estimate info... FROM [lib://.......last year]

Any suggestions would be greatly appreciated.

[Oleg_Troyansky · Accepted solution]

Hi Joe,

Yes, you can use the Exists() function:

LOAD

...

WHERE

    NOT Exists([Estimate ID])

;

Cheers,

[JoeCharter]

That worked great!  Thanks for the tip.