Compare IF one field has no matches in other column

[eugeniyaromanov]

Hello everyone.

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

They are like:

First:

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

Second:

Number	AddressSecondDatabase
1	Italy|Matelica
1	Italy|Poggio San Marcello
1	Italy|Montalcino
1	Italy|Aralet Di Calco
1	Italy|Cazzago San Martino
1	Italy|Ronca
1	Italy|SM BUON ALBERGO
1	Italy|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:

Number	AddressFirstDatabase	AddressSecondDatabase
1	Italy|Cazzago San Martino	Italy|Cazzago San Martino
1	Italy|Matelica	Italy|Matelica
1	Italy|Montalcino	Italy|Montalcino
1	Italy|Poggio San Marcello	Italy|Poggio San Marcello
1	Italy|Ronca	Italy|Ronca
1	Italy|Calco	-
1	-	Italy|Aralet Di Calco
1	Italy|Orsenigo	-
	-	Italy|SM BUON ALBERGO
1	-	Italy|ORSENIGO
1	Italy|San Martino Buon Albergo	-
2	France|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!

[avinashelite]

how about adding new column like this

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

[eugeniyaromanov]

In script?

[avinashelite]

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

[eugeniyaromanov]

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]

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 · Accepted solution]

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

[eugeniyaromanov]

Thanks for an idea!

Works perfect!