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

[Anonymous]

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,

[hector_munoz · Accepted solution]

Hi Abhay,

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

T1:

LOAD * INLINE [

    F1

    One

    Two

    Three

    Four

];

T2:

LOAD * INLINE [

    F2

    One

    Two

];

T3:

LOAD     F1 AS F3

RESIDENT T1

WHERE    Not(Exists(F2, F1));

I hope it serves to you.

Regards,
H

[maxgro]

another option could be

T1:

LOAD * INLINE [

    F1

    One

    Two

    Three

    Four

];

T2:

LOAD * INLINE [

    F2

    One

    Two

];

Result:

LOAD F1 as Field, 1 as InT1 Resident T1;

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

[MarcoWedel]

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

[Anonymous]

Thanks for helping out. This works for me.

[MarcoWedel]

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