joining two tables to get the changed value in the table

[Anonymous]

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

[its_anandrjs]

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;

[maxgro]

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];

[swuehl]

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;

[its_anandrjs]

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

[Anonymous]

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.

[Anonymous]

also i want a solution which returns me '6' as my answer (which is in table 1 and not in table 2).

[its_anandrjs · Accepted solution]

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;

[maxgro]

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;

[swuehl]

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.