Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to get non matching values?

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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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;

View solution in original post

9 Replies
sunny_talwar

What is the final output you are looking to get?

Anonymous
Not applicable
Author

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

Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

Hi Shiva,

It worked thanks .

Not applicable
Author

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
Not applicable
Author

Yes Manoj it worked.

Anonymous
Not applicable
Author

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;