topic Re: How to get non matching values? in QlikView

How to get non matching values?

Anonymous — Wed, 19 Apr 2017 22:36:31 GMT

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

Re: How to get non matching values?

sunny_talwar — Wed, 19 Apr 2017 23:58:18 GMT

What is the final output you are looking to get?

Re: How to get non matching values?

Anonymous — Thu, 20 Apr 2017 02:18:58 GMT

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

Re: How to get non matching values?

Thu, 20 Apr 2017 02:59:38 GMT

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;

Re: How to get non matching values?

Anonymous — Thu, 20 Apr 2017 03:10:32 GMT

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

Re: How to get non matching values?

Anonymous — Thu, 20 Apr 2017 03:32:07 GMT

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

NoConcatenate

Load * Resident Table1;

join

Load ID,State as State2 Resident Table2;

drop Table Table2;

drop table Table1;

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;

Re: How to get non matching values?

Anonymous — Thu, 20 Apr 2017 04:27:55 GMT

Hi Shiva,

It worked thanks .

Re: How to get non matching values?

Thu, 20 Apr 2017 04:31:20 GMT

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;

Re: How to get non matching values?

Anonymous — Thu, 20 Apr 2017 04:45:18 GMT

Yes Manoj it worked.

Re: How to get non matching values?

Anonymous — Thu, 20 Apr 2017 04:48:01 GMT

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;