conditional showing of expression values

[#1]

Hi Community,

I have two tables of similar data, here i need to reconcile the data between two tables if the amount between the two tables does not match then i need to show that values

Input:

Tab1:

id,region,amount

101,America,2000

102,Asia,4000

103,Africa,7000

104,Europe,9000

Tab2:

temp_id,Temp_region,Temp_amount

101,America,2000

102,Asia,2000

103,Africa,7000

104,Europe,8500

output:

id,region,amount

102,Asia,2000

104,Europe,500

[sunny_talwar · Accepted solution]

May be like this:

Tab1:

LOAD *,

  AutoNumber(id&region&amount) as Key;

LOAD * Inline [

id,region,amount

101,America,2000

102,Asia,4000

103,Africa,7000

104,Europe,9000

];

Tab2:

LOAD * Inline [

temp_id,Temp_region,Temp_amount

101,America,2000

102,Asia,2000

103,Africa,7000

104,Europe,8500

]

Where not Exists(Key, AutoNumber(temp_id&Temp_region&Temp_amount));

DROP Table Tab1;

[narendiran]

Hi,

Hope this helps

Tab1:

id,region,amount

101,America,2000

102,Asia,4000

103,Africa,7000

104,Europe,9000

Left Join (Tab1)

temp_id,Temp_region,Temp_amount

101,America,2000

102,Asia,2000

103,Africa,7000

104,Europe,8500;

Output:

Load

id, region,  amount-Temp_amount as amount

Resident Tab1

where  id = temp_id;

Drop Table Tab1;

[maxgro]

another solution, using join and right keep

Tab1:

LOAD * Inline [

id,region,amount

101,America,2000

102,Asia,4000

103,Africa,7000

104,Europe,9000

];

join (Tab1)

LOAD temp_id as id, Temp_region as region, Temp_amount  Inline [

temp_id, Temp_region, Temp_amount

101,America,2000

102,Asia,2000

103,Africa,7000

104,Europe,8500

]

;

right Keep (Tab1)

load *, amount - Temp_amount

Resident Tab1

where amount <> Temp_amount;