Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
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!