Qlik Community

Ask a Question

Connectivity & Data Prep

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
schuffe
Contributor III
Contributor III

Concatenate Load where not exists

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:

schuffe_1-1624947023208.png

 

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:

schuffe_2-1624947277170.png

 

How can I fix the script ?

Thanks in advance 🙂

 

1 Solution

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

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 

View solution in original post

4 Replies
PrashantSangle

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

Great dreamer's dreams never fulfilled, they are always transcended.
schuffe
Contributor III
Contributor III
Author

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
Master III
Master III

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

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

Drop table DataOld2;

May you live in interesting times!
marcus_sommer
MVP & Luminary
MVP & Luminary

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 

View solution in original post