Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
eugeniyaromanov
Contributor III
Contributor III

Compare IF one field has no matches in other column

Hello everyone.

I have two tables with addresses and I need to find differences.

They are like:

First:

NumberAddressFirstDatabase
1Italy|Matelica
1Italy|Poggio San Marcello
1Italy|Montalcino
1Italy|Calco
1Italy|Orsenigo
1Italy|Cazzago San Martino
1Italy|San Martino Buon Albergo
1Italy|Ronca
2France|Chateaubernard
2France|Gensac la Pallue

Second:

NumberAddressSecondDatabase
1Italy|Matelica
1Italy|Poggio San Marcello
1Italy|Montalcino
1Italy|Aralet Di Calco
1Italy|Cazzago San Martino
1Italy|Ronca
1Italy|SM BUON ALBERGO
1Italy|ORSENIGO
2-

This is two different tables. They are related with field Number, but they have different row numbers and everything else.

I can easy find if addresses are the same:

=if(AddressFirstDatabase=AddressSecondDatabase, AddressFirstDatabase) and hide Null.

Like this:

Italy|Cazzago San Martino

Italy|Matelica

Italy|Montalcino

Italy|Poggio San Marcello

Italy|Ronca

But I need to find difference.

I need to get table like this:

NumberAddressFirstDatabaseAddressSecondDatabase
1Italy|Cazzago San MartinoItaly|Cazzago San Martino
1Italy|MatelicaItaly|Matelica
1Italy|MontalcinoItaly|Montalcino
1Italy|Poggio San MarcelloItaly|Poggio San Marcello
1Italy|RoncaItaly|Ronca
1Italy|Calco-
1-Italy|Aralet Di Calco
1Italy|Orsenigo-
-Italy|SM BUON ALBERGO
1-Italy|ORSENIGO
1Italy|San Martino Buon Albergo-
2France|Chateaubernard-
2-France|Gensac la Pallue

It can be different table. It can be even three tables.

But I need to find IF one address has no matches in other column at all.

Thanks in advance!

1 Solution

Accepted Solutions
maxgro
MVP
MVP

after your script add

X:

load FirstTableAddress as Address, 1 as T1 Resident Table1;

join (X) load SecondTableAddress as Address, 1 as T2 Resident Table2;


You get a table with same address (T1=1, T2=1) and different address

1.png

View solution in original post

7 Replies
avinashelite

how about adding new column like this

=if(AddressFirstDatabase=AddressSecondDatabase, '',AddressSecondDatabase) as AddressSecondDatabase

eugeniyaromanov
Contributor III
Contributor III
Author

In script?

avinashelite

Yes try in script ..if its not working share the sample app

eugeniyaromanov
Contributor III
Contributor III
Author

I better put the sample, I can't figure out how to do this in script...

May be I just can't see something obvious...

eduardo_dimperio
Specialist II
Specialist II

Hi Eugeniya,

Try this

Aux:

Load

*,

if(AddressFirstDatabase=AddressSecondDatabase, 'OK','NOT_OK') AS Check

From

YourQVD;


NoConcatenate

Check:

Load

*

Resident Aux

Where Check='NOT_OK'


Drop Table Aux;



This will result only rows where adress are different.





maxgro
MVP
MVP

after your script add

X:

load FirstTableAddress as Address, 1 as T1 Resident Table1;

join (X) load SecondTableAddress as Address, 1 as T2 Resident Table2;


You get a table with same address (T1=1, T2=1) and different address

1.png

eugeniyaromanov
Contributor III
Contributor III
Author

Thanks for an idea!

Works perfect!