topic Re: Concatenate Load where not exists in Connectivity & Data Prep

Concatenate Load where not exists

schuffe — Tue, 29 Jun 2021 06:16:45 GMT

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 🙂

Re: Concatenate Load where not exists

PrashantSangle — Tue, 29 Jun 2021 06:26:10 GMT

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

Re: Concatenate Load where not exists

schuffe — Tue, 29 Jun 2021 06:34:32 GMT

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.

Re: Concatenate Load where not exists

oknotsen — Wed, 30 Jun 2021 06:46:20 GMT

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

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

Drop table DataOld2;

Re: Concatenate Load where not exists

marcus_sommer — Thu, 01 Jul 2021 12:17:36 GMT

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