Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone, I have two tables with same column name
Table 1:
Load * Inline [
A,
1,
2,
3,
4,
5,
6];
Table 2:
Load * Inline[
A,
1,
2,
3,
4,
5,
7];
I want a solution which returns me 7 as the answer.
I tried this
Load * from Table1;
right Join
load* from table2
where not exists(A);
but when i reload this script of mine , its reloading perfectly but not fetching 0 rows .
Thanking everyone in anticipation
Then try to load like below script so from this you get 7 which is only in Table2
Table1:
Load * Inline
[
A
1
2
3
4
5
6
];
Table2:
Load * Inline
[
AA
1
2
3
4
5
7];
NoConcatenate
Data:
Load A Resident Table1;
Right Join
Load AA as A Resident Table2
where not exists(A,AA);
//If you not need the Table1 and Table2 then drop this tables like
DROP Table Table1;
DROP Table Table2;
EXIT Script;
Try to load your table like below
Table1:
Load * Inline
[
A
1
2
3
4
5
6
];
Table2:
Load * Inline
[
AA
1
2
3
4
5
7];
NoConcatenate
Data:
Load A Resident Table1;
Right Join
Load AA as A Resident Table2
where not exists(A,AA);
If you not need the Table1 and Table2 then drop this tables like
DROP Table Table1;
DROP Table Table2;
Table1:
Load * Inline [
A,
1,
2,
3,
4,
5,
6];
Table2:
NoConcatenate
Load AA as A where not exists(A, AA);
Load * Inline [
AA,
1,
2,
3,
4,
5,
7];
So you want to pick the values that are in table2 but not in table1, right?
So I think there is not need to join the tables, because they don't share any common key values.
Try maybe like this:
Table1:
LOAD A From Table1;
Table2:
NOCONCATENATE LOAD A FROM Table2 where not exists(A);
drop table Table1;
Another very simple way of doing this also as from requirement you want to keep only the & from Table2 then load table like below
Table1:
Load * Inline
[
A
1
2
3
4
5
6
];
NoConcatenate
Table2:
Load * Inline
[
A
1
2
3
4
5
7
]Where not Exists(A,A);
Regards
thank you all for ur reply...but, i m still not getting what i want. I want only '7'(which is in table 2 but not in table 1) as my answer.
also i want a solution which returns me '6' as my answer (which is in table 1 and not in table 2).
Then try to load like below script so from this you get 7 which is only in Table2
Table1:
Load * Inline
[
A
1
2
3
4
5
6
];
Table2:
Load * Inline
[
AA
1
2
3
4
5
7];
NoConcatenate
Data:
Load A Resident Table1;
Right Join
Load AA as A Resident Table2
where not exists(A,AA);
//If you not need the Table1 and Table2 then drop this tables like
DROP Table Table1;
DROP Table Table2;
EXIT Script;
Table1:
Load * Inline [
A,
1,
2,
3,
4,
5,
6];
Table2:
NoConcatenate
Load * Inline [
A,
1,
2,
3,
4,
5,
7];
Common:
NoConcatenate load A as AA Resident Table1;
inner join load A as AA Resident Table2;
Table1Final:
NoConcatenate load * Resident Table1 where not Exists (AA, A);
DROP Table Table1;
Table2Final:
NoConcatenate load * Resident Table2 where not Exists (AA, A);
DROP Table Table2;
DROP Table Common;
Karunpreet Soni wrote:
thank you all for ur reply...but, i m still not getting what i want. I want only '7'(which is in table 2 but not in table 1) as my answer.
I do get 7 with the approach I posted above. What do you get?
Note that you shouldn't do a resident load, load the tables directly (or rename the fields when loading from a resident table).
If you want the values from table1 that are not in table2, just exchange the table names in the script snippet.