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: 
Not applicable

Show difference between two tables!

Hi Community,

I have two tables like this:

Unbenannt.JPG

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

A Listbox solution is also ok.

Unbenannt.JPG

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

;

Unbenannt.JPG

Thanks in advance!!!

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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);


talk is cheap, supply exceeds demand

View solution in original post

5 Replies
Gysbert_Wassenaar

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);


talk is cheap, supply exceeds demand
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;

Not applicable
Author

Wow great you're awesome!

Not applicable
Author

Nice answer!

Amit
Contributor III
Contributor III

Amazing! Thank you!