topic Re: How to load only uncommon records from the data set in Connectivity & Data Prep

How to load only uncommon records from the data set

ishika00730 — Mon, 26 Nov 2018 02:30:01 GMT

Hi,

I have two tables to load in my application. The requirement is to load only the uncommon records that exist in Table 2 .The records need not to be in my application.

Please find the below script and suggest how can I achieve this result.

a:
LOAD * INLINE [
ID, Name
1, A
2, B
3, C
4, D
5, E
];

B:
LOAD * INLINE [
ID, Name
1, A
2, B
3, C
4, D
5, E
6, F
7, G
8, H
] ;

Output : I need to have only IDs- 6,7,8 in my application rest all the common IDs to be dropped.

Thanks

Ishika

Re: How to load only uncommon records from the data set

saurabhwadhwa — Mon, 26 Nov 2018 03:52:11 GMT

Hi,

Please try below script.

a:
LOAD * INLINE [
ID, Name
1, A
2, B
3, C
4, D
5, E
];

NoConcatenate
B:
LOAD * WHERE NOT EXISTS ( ID,ID);
LOAD * INLINE [
ID, Name
1, A
2, B
3, C
4, D
5, E
6, F
7, G
8, H
] ;

DROP TABLE a;

Hope this helps you.

BR,

Saurabh

Re: How to load only uncommon records from the data set

vardhancse — Mon, 26 Nov 2018 05:14:41 GMT

Hi,

Adding to above can try something like mentioned below:

Main1:
LOAD * INLINE [
ID, Name
1, A
2, B
3, C
4, D
5, E
];

join
Main2:
LOAD * INLINE [
ID1, Name
1, A
2, B
3, C
4, D
5, E
6, F
7, G
8, H
];

NoConcatenate

Main:
Load *
Resident Main1
where IsNull(ID) or IsNull(ID1);

Drop Table Main1;

PFA sample app for your reference:

Re: How to load only uncommon records from the data set

JaMajka1 — Mon, 26 Nov 2018 08:10:58 GMT

Hi,
I would do it also by using where not exists(). Just for being efficient - if both fields have the same name you can write it only once. So the condition where not exists(ID) works the same ;).
BR,
Maria