topic Re: Show difference between two tables! in QlikView

Show difference between two tables!

Wed, 25 Nov 2020 16:16:04 GMT

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!!!

Re: Show difference between two tables!

Gysbert_Wassenaar — Fri, 15 Jul 2016 12:21:55 GMT

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

Re: Show difference between two tables!

sunny_talwar — Fri, 15 Jul 2016 12:22:50 GMT

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;

Re: Show difference between two tables!

Fri, 15 Jul 2016 12:44:44 GMT

Wow great you're awesome!

Re: Show difference between two tables!

Fri, 15 Jul 2016 12:45:08 GMT

Nice answer!

Re: Show difference between two tables!

Amit — Tue, 18 Apr 2023 11:41:02 GMT

Amazing! Thank you!