Show difference between two tables!

[#1]

Hi Community,

I have two tables like this:

Now I want a third table which shows that OI5 is not in "Table1"!

A Listbox solution is also ok.

I tried some things with joins bit it didn't work. I also read How to compare 2 tables and put non-matching results?

but it only showed me the difference between the two tables in a listbox. See below:

Table1:

LOAD * INLINE [

OrderItem

OI1

OI2

OI3

OI4

OI6

];

Concatenate(Table1)

Table2:

LOAD * INLINE [

OrderItem

OI2

OI3

OI4

OI5

OI6

];

INNER JOIN (Table1)

LOAD *

WHERE "Only in One Table?"

;

// Here I want "OI5" as an output

Difference:

LOAD

  OrderItem,

  if(count(OrderItem)<2,-1) as "Only in One Table?"

RESIDENT Table1 GROUP BY OrderItem

;

Thanks in advance!!!

[Gysbert_Wassenaar · Accepted solution]

Table1:

LOAD OrderItem, OrderItem as OI1 INLINE [

OrderItem

OI1

OI2

OI3

OI4

OI6

];

Table2:

LOAD OrderItem, OrderItem as OI2 INLINE [

OrderItem

OI2

OI3

OI4

OI5

OI6

];

Table3:

LOAD OrderItem, 'Not in Table 2' as WhatsUpWithThisRecord RESIDENT Table1 WHERE Not Exists(OI2, OrderItem);

LOAD OrderItem, 'Not in Table 1' as WhatsUpWithThisRecord RESIDENT Table2 WHERE Not Exists(OI1, OrderItem);

[sunny_talwar]

May be like this:

Table1:

LOAD * INLINE [

OrderItem

OI1

OI2

OI3

OI4

OI6

];

Table2:

LOAD * INLINE [

OrderItem2

OI2

OI3

OI4

OI5

OI6

];

Difference:

LOAD OrderItem2 as OrderItemDifference

Resident Table2

Where Not Exists(OrderItem, OrderItem2);

Concatenate(Table1)

LOAD OrderItem2 as OrderItem

Resident Table2;

DROP Table Table2;

[#4]

Wow great you're awesome!

[#5]

Nice answer!

[Amit]

Amazing! Thank you!