Concatenate Load where not exists

[schuffe]

Hello everyone 🙂

I have 2 sets of data (Data old, Data new)

Now I want to Load everything from 'Data new' and concatenate it with the data from 'Data old' where the number does not exist in Data new.

Small example for the 2 sets:

My Code looks like this:

LOAD *

From Data new;

Concatenate

LOAD *

From Data old

Where Not Exists(Number);

And I get the following Concatenated data:

How can I fix the script ?

Thanks in advance 🙂

[PrashantSangle]

what is wrong in output??? it is giving you result as per your requirement.

[schuffe]

No not 100 %  🙂

You can compare it to the right table in the first picture.

The data with number '2' and '3' only loaded once. I want to laod every row.

[oknotsen]

DataOld2: noconcatenate Load number, sum(Profit) as Profit resident DataOld group by number;

Concatenate LOAD * From DataOld2 Where Not Exists(Number);

Drop table DataOld2;

[marcus_sommer · Accepted solution]

Your approach with not exists(KeyField) worked only if the values within the KeyField are really unique.  If they are not unique you will need more or less expensive extra steps in making the key unique, doing some pre-loads for separate filtering or probably the easiest way is using exists() with two parameters like:

LOAD *, Number as KeyNumber

From Data new;

Concatenate

LOAD *

From Data old

Where Not Exists(KeyNumber, Number);

drop fields KeyNumber;

The disadvantage is that your loads - if they come from qvd's - aren't optimized anymore. But like mentioned you will need always additionally efforts and any alternative methods will also cost some performance/run-time.

If the newest data adding only new data and contain no update to the old data it's not necessary to load the newest data at first so that you could reverse the load order and then you could use exists() instead of not exists().

- Marcus