Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Find the difference between two tables

Hi, I have two data sets that I have to compare and extract the data from both tables that are NOT in the other. I do not know how to do that . Can anyone help me?

As you can see there are 4 rows in one and 5 in the second. Four of the records are identical when coparing "WEXAM_DATE and "UndersøgelsesDato"

6 Replies
rupamjyotidas
Valued Contributor

Re: Find the difference between two tables

Not applicable

Re: Find the difference between two tables

Thanks, I have seen it, but I can not find out how to "convert" this into something useable  for me :-(

I have theses to tables but I do not know how to make it work.

ODBC CONNECT32 TO POD_DW;

SQL SELECT

    "WEXAM_DATE" as Field1,

    "WEXAM_ID" as Field2

   

FROM "POD_DW".dbo."W_EXAM";

ODBC CONNECT32 TO GammeltPOD;

SQL SELECT

    "ExamID" as Field2,

    "UndersøgelsesDato" as Field1

   

FROM Kunder.dbo."Kunde_Exams";

shraddha_g
Honored Contributor III

Re: Find the difference between two tables

Do you mean you want to get record(EXAMID) from another table where WEXAM_DATE != "UndersøgelsesDato" ?

Not applicable

Re: Find the difference between two tables

I have two tables

W_EXAM

with the datafields:

WEXAM_DATE

WEXAM_ID

and

Kunde_Exams

with the datafields:

ExamID

UndersøgelsesDato

What I want is to compare
WEXAM_DATE with UndersøgelsesDato

and

WEXAM_ID with ExamID

If I find ANY of these that are NOT matched I want a list of the "orphant" records.

mov
Esteemed Contributor III

Re: Find the difference between two tables

Maybe this:

// Load separately both tables

ODBC CONNECT32 TO POD_DW;

Table1:

SQL SELECT

    "WEXAM_DATE" as Field1,

    "WEXAM_ID"

FROM "POD_DW".dbo."W_EXAM";

ODBC CONNECT32 TO GammeltPOD;

Table2:

SQL SELECT

    "ExamID",

    "UndersøgelsesDato" as Field1

FROM Kunder.dbo."Kunde_Exams";

// Find intersection

Temp:

INNER KEEP (Table1) LOAD

  Field1 as Intersection

RESIDENT Table2;

//  Load from the original tables excluding common records

Result:

LOAD

  Field1,

  "WEXAM_ID" as Field2

RESIDENT Table1

WHERE not exists(Intersection, Field1);

CONCATENATE (Result) LOAD

  Field1,

  "ExamID" as Field2

RESIDENT Table2

WHERE not exists(Intersection, Field1);

DROP TABLES Table1, Table2, Temp;

shraddha_g
Honored Contributor III

Re: Find the difference between two tables

Try:

Table1:

Load

    "WEXAM_DATE" ,

    "WEXAM_ID"    

FROM Table1;

Table2:

Load

    "ExamID" as ID,

    "UndersøgelsesDato" as Exam_Date

FROM Table2

where not exists("WEXAM_DATE","UndersøgelsesDato") and not exists( "WEXAM_ID","ExamID");

Drop table Table1;

Community Browser