Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
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
MVP & Luminary
MVP & Luminary

Re: Show difference between two tables!

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

4 Replies
MVP & Luminary
MVP & Luminary

Re: Show difference between two tables!

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

Re: Show difference between two tables!

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

Re: Show difference between two tables!

Wow great you're awesome!

Not applicable

Re: Show difference between two tables!

Nice answer!