Finding differences between fields from 2 tables with the same key field

[tinkerz1]

Hi,

How do you check for matches on fields with a common key, this following statements takes to long

if(
if(([Key1]=[Key2]) and ([A1]=[B1]) and([A2]=[B2]),'Match','No Match')='No Match','No Match'
)

My problems started when I wanted to change Key Syn field to Key 1, this would make a key across 2 fields

inner join (Table2)
load
[ID1],
[Key Syn] as [Key1]
RESIDENT Concate_Open_And_Others_S2;

So I thought this expression would work:

if(
if(([A1]=[B1]) and([A2]=[B2]),'Match','No Match')='No Match','No Match'
)

But B1 and B2 brought in more information, I have checked and I only have 1 unique row per Key.

Have I made a mistake in this line:

[Key Syn] as [Key1] in the inner join; this is where I make the key field on table 2 to make my matching formula to work.

In other words I have not got the correct logic to make a key on 2 tables and check the differences in other fields, there are 2 files in this instance that I run through a load script, I do the same process on both, its just one file is older than the other and I am looking for changes in 2 fields, so I use a key field to 'Join' the tables.

Whats wrong with my logic?

[kaushiknsolanki]

Hi Neil,

Let me understand the purpose of this.

Do you mean that you have two files one is Old and one is New, both with identical fields. And you want to create a new table where you combine the values from both the tables and remove duplicate values?

Regards.

Kaushik Solanki

[tinkerz1]

No its just checking 2 tables for changes historically.

I make a key from Table A

Col A,Col B,Col C,Col D = Table A Key1

and want to check 2 fields in this table.

Data 1 and Data 2.

Against an earlier version of the table.

Col A,Col B,Col C,Col D = Table B Key1

and want to check against 2 fields in this table.

'Data 1 source 2' and 'Data 2 source 2'

Thats all.

[kaushiknsolanki]

Dear Neil,

In that case what you have done is right, but you must also change the filed name. Say for example you script should be.

XXX:

Load Key,

        Field1,

        Field2

From ABC

Inner Join

Load Key,

        Field1 as Check1

        Field2 as Check2

From

XYZ;

ZZZ:

Load Key, if(Field1 = Check1, 'Matched','Not Matched') Field1_Validation,

               if(Field2 = Check2, 'Matched','Not Matched') Field2_Validation

Resident XXX;

Drop table XXX;

Regards,

Kaushik Solanki

[jonathandienst]

I usually load both sets into the same table with a 'Source' field.

Data:

LOAD ColA, ColB, ColC, ColD, Data1, Data2,

  'Table1' As Source;

SQL SELECT * FROM Table1;

Concatenate(Data)

LOAD ColA, ColB, ColC, ColD, Data1, Data2,

  'Table2' As Source;

SQL SELECT * FROM Table2;

Now create a table with ColA, ColB, ColC, ColD as dimensions, and for the expressions:

  =Sum({<Source={'Table1'}>} Data1)

  =Sum({<Source={'Table1'}>} Data2)

  =Sum({<Source={'Table2'}>} Data1)

  =Sum({<Source={'Table2'}>} Data2)

and/or

  =Sum({<Source={'Table1'}>} Data1) - Sum({<Source={'Table2'}>} Data1)

  =Sum({<Source={'Table1'}>} Data2) - Sum({<Source={'Table2'}>} Data2)

This technique performs well for large data comparisons with several million rows in each table.

[tinkerz1]

Hi Jonathan,

Data1 and Data2 are 'low' 'medium' ' high'

So I am checking changes in strings on unique Keys, thats if the key appears in the new source data.

[jonathandienst]

Well if they are unique for the dimensions ColA- ColD, use Only(), or use Concat () if they are not

Only({<Source={'Table1'}>} Data1)

or

Concat({<Source={'Table1'}>} DISTINCT Data1, ',')

or

Count({<Source={'Table1'}>} Data1)

[tinkerz1]

so if I want to find the diffences I would write

if(Only({<Source={'Table1'}>} Data1)&Only({<Source={'Table1'}>} Data2) <> Only({<Source={'Table2'}>} Data1)&Only({<Source={'Table2'}>} Data2) ,'No Match')

Is that it? I am still trying to understand