How to get non matching values?

[Anonymous]

Hi I have 2 tables as shown below.

Table1:

LOAD *

  INLINE [

  ID, State

  1, Delaware

  2, Pennsylvania

  3, New Jersey

  4, Georgia

  5, Connecticut

  6, Massachusetts

  7, Maryland

  8, South Carolina

  9, New Hampshire

  10, Virginia

  11, New York];

Table2:

NoConcatenate

LOAD *

  INLINE [

  ID, State

  1, Delaware

  2, Pennsylvania

  3, New Jersey

  4, Georgia

  5, California

  6, Massachusetts

  7, Maryland

  8, South Carolina

  9, New Hampshire

  10, Minnesota

  11, New York];

I need to get those IDs that have different states from both tables but have same ID. How can I get it?

Thanks,

Vishnu

[sunny_talwar]

What is the final output you are looking to get?

[Anonymous]

Hi Sunny,

I need output in the following form.

Table:

ID, State

5, Connecticut

5, California

10, Virginia

10, Minnesota

So these are the rows with different states for the same ID.

Thanks,

Vishnu

[#4]

Try like this

Table1:

LOAD *

  INLINE [

  ID, State

  1, Delaware

  2, Pennsylvania

  3, New Jersey

  4, Georgia

  5, Connecticut

  6, Massachusetts

  7, Maryland

  8, South Carolina

  9, New Hampshire

  10, Virginia

  11, New York];

TMP:

LOAD *,

ID&NAME AS KEY

RESIDENT Table1;

DROP TABLE Table1;

Table2:

NoConcatenate

LOAD *

  INLINE [

  ID, State

  1, Delaware

  2, Pennsylvania

  3, New Jersey

  4, Georgia

  5, California

  6, Massachusetts

  7, Maryland

  8, South Carolina

  9, New Hampshire

  10, Minnesota

  11, New York]

where not exist(KEY,ID&NAME)) ;

DROP TABLE TMP;

[Anonymous]

Hi Manoj,

I tried that code before:

Table1:

LOAD *

  INLINE [

  ID, State

  1, Delaware

  2, Pennsylvania

  3, New Jersey

  4, Georgia

  5, Connecticut

  6, Massachusetts

  7, Maryland

  8, South Carolina

  9, New Hampshire

  10, Virginia

  11, New York

  12, North Carolina

  13, Rhode Island

  14, Vermont

  15, Kentucky

  16, Tennessee

  17, Ohio

  18, Louisiana

  19, Indiana

  20, Mississippi

  21, Illinois

  22, Alabama

  23, Maine

  24, Missouri

  25, Arkansas

  26, Michigan

  27, Florida

  28, Texas

  29, Iowa

  30, Wisconsin ];

Table2:

NoConcatenate

LOAD *

  INLINE [

  ID, State

  1, Delaware

  2, Pennsylvania

  3, New Jersey

  4, Georgia

  5, California

  6, Massachusetts

  7, Maryland

  8, South Carolina

  9, New Hampshire

  10, Minnesota

  11, New York

  12, North Carolina

  13, Rhode Island

  14, Vermont

  15, Oregon

  16, Tennessee

  17, Ohio

  18, Louisiana

  19, Indiana

  20, Kansas

  21, Illinois

  22, Alabama

  23, Maine

  24, Missouri

  25, West Virginia

  26, Michigan

  27, Florida

  28, Texas

  29, Iowa

  30, Nevada ]

where not exists (State,State) ;

Table3:

Load ID,

  State

Resident Table1

Where not Exists(State,State);

DROP Table Table1;

Output for it contains only the fields from Table2.

But that's not the output I am looking for. I am look for something like below.

Table2:

ID, State

5, Connecticut

5, California

10, Virginia

10, Minnesota

[Anonymous · Accepted solution]

There will definitely be a better solution. But this works.

Table1:

LOAD *

  INLINE [

  ID, State

  1, Delaware

  2, Pennsylvania

  3, New Jersey

  4, Georgia

  5, Connecticut

  6, Massachusetts

  7, Maryland

  8, South Carolina

  9, New Hampshire

  10, Virginia

  11, New York];

Table2:

NoConcatenate

LOAD *

  INLINE [

  ID, State

  1, Delaware

  2, Pennsylvania

  3, New Jersey

  4, Georgia

  5, California

  6, Massachusetts

  7, Maryland

  8, South Carolina

  9, New Hampshire

  10, Minnesota

  11, New York];

  T:

  NoConcatenate

  Load * Resident Table1;

  join

  Load ID,State as State2  Resident Table2;

    drop Table Table2;

    drop table Table1;

    P:

NoConcatenate

Load * Resident  T where State2 <> State;

Load ID,State Resident P;

Load ID,State2 as State Resident P;

drop table P;

drop table T;

[Anonymous]

Hi Shiva,

It worked thanks .

[#8]

This also works ....

Table1:
LOAD *

INLINE [

ID, State

1, Delaware

2, Pennsylvania

3, New Jersey

4, Georgia

5, Connecticut

6, Massachusetts

7, Maryland

8, South Carolina

9, New Hampshire

10, Virginia

11, New York];


Table2:

NoConcatenate

LOAD *

INLINE [

ID, State

1, Delaware

2, Pennsylvania

3, New Jersey

4, Georgia

5, California

6, Massachusetts

7, Maryland

8, South Carolina

9, New Hampshire

10, Minnesota

11, New York] ;

tt:
load ID,State as s1 Resident Table1 ;
NoConcatenate
Final1:
load ID,State  Resident Table2 where not Exists(s1,State);
tt1:
load ID,State as s3 Resident Table2 ;
Concatenate(Final1)
Final2:
load ID,State  Resident Table1 where not Exists(s3,State);
drop Table Table1,Table2,tt,tt1;

[Anonymous]

Yes Manoj it worked.

[Anonymous]

I modified the script that you guys suggested as the script was taking somewhat longer time to reload.

Table1:

LOAD *

  INLINE [

  ID, State

  1, Delaware

  2, Pennsylvania

  3, New Jersey

  4, Georgia

  5, Connecticut

  6, Massachusetts

  7, Maryland

  8, South Carolina

  9, New Hampshire

  10, Virginia

  11, New York];

Table2:

join

LOAD ID,

  State as State2

  INLINE [

  ID, State

  1, Delaware

  2, Pennsylvania

  3, New Jersey

  4, Georgia

  5, California

  6, Massachusetts

  7, Maryland

  8, South Carolina

  9, New Hampshire

  10, Minnesota

  11, New York];

Temp:

NoConcatenate

Load * Resident  Table1 where State2 <> State;

drop table Table1;

Table:

Load ID,State Resident Temp;

Load ID,State2 as State Resident Temp;

DROP Table Temp;