topic Re: List difference / delta between 2 columns in 2 different tables in load script in QlikView

List difference / delta between 2 columns in 2 different tables in load script

Anonymous — Thu, 26 Jan 2017 17:14:07 GMT

Hi There,

Is there a way to list differences between 2 tables in the load script.

Table 1:

Name	Value
One	10
Two	20
Three	30
Four	40

Table 2:

Name
One
Two

I am trying to list the missing values in Table 2

The desired result should be: "Three" and "Four"

Thanks,

Re: List difference / delta between 2 columns in 2 different tables in load script

hector_munoz — Thu, 26 Jan 2017 17:24:25 GMT

Hi Abhay,

Here you have a sample using Not(Exists()) functions:

T1:

LOAD * INLINE [

One

Two

Three

Four

];

T2:

LOAD * INLINE [

One

Two

];

T3:

LOAD F1 AS F3

RESIDENT T1

WHERE Not(Exists(F2, F1));

I hope it serves to you.

Regards,
H

Re: List difference / delta between 2 columns in 2 different tables in load script

maxgro — Thu, 26 Jan 2017 17:31:28 GMT

another option could be

T1:

LOAD * INLINE [

One

Two

Three

Four

];

T2:

LOAD * INLINE [

One

Two

];

Result:

LOAD F1 as Field, 1 as InT1 Resident T1;

join (Result) load F2 as Field, 1 as InT2 Resident T2;

Re: List difference / delta between 2 columns in 2 different tables in load script

MarcoWedel — Sat, 28 Jan 2017 22:40:46 GMT

Hi,

yet another solution might be:

Table2:
LOAD Name,
    Name as NameTable2
FROM [https://community.qlik.com/thread/247714] (html, codepage is 1252, embedded labels, table is @2);

Table1:
LOAD *,
    -Exists(NameTable2, Name) as IsInTable2
FROM [https://community.qlik.com/thread/247714] (html, codepage is 1252, embedded labels, table is @1);

DROP Field NameTable2;

hope this helps

regards

Marco

Re: List difference / delta between 2 columns in 2 different tables in load script

Anonymous — Sat, 28 Jan 2017 22:45:03 GMT

Thanks for helping out. This works for me.

Re: List difference / delta between 2 columns in 2 different tables in load script

MarcoWedel — Sat, 28 Jan 2017 22:53:09 GMT

you're welcome

glad it helped

please close your thread if your question is answered:

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco