Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

how to get the unmatched data in two tables

Hi,

I have two tables from this i need to fetch the unmatched date fields a new table(Table3).

Table1:

 

5/15/2017
5/16/2017
5/17/2017
5/18/2017
5/19/2017
5/20/2017

Table2:

 

Date
5/17/2017
5/18/2017
5/19/2017
5/20/2017
5/21/2017
5/22/2017

My expected output table:

  Table 3:

5/15/2017
5/16/2017
5/21/2017
5/22/2017

thanks,

yuvaraj g

11 Replies
sibusiso90
Creator III
Creator III

Thanks

plexpro52
Creator
Creator

While I am often amazed at what can be done in Qlik scripting language, often solutions seem to me to be "tricky", not elegant or well-related to the abstract problem.  Thus, I would much prefer dealing directly with set language.  The following solution to me seems to approach that ideal.  I have used two tables as source for the date.  Ideally, I would give the dates different names according to their respective table (e.g., "Date1" and "Date2"), but in the interest of generalizing the solution, I have kept the same name for each.

First I get the intersection of the source tables into "Intersection", and then I reload the source tables into "Complement" if its respective date not already exist in "Intersection".

This is my first foray into this, perhaps someone else may propose something even better.

DateSourceTable1:

LOAD * INLINE [

Date

5/17/2017,

5/18/2017,

5/19/2017,

5/20/2017,

5/21/2017

];

NoConcatenate

DateSourceTable2:

LOAD * INLINE [

Date

5/15/2017,

5/16/2017,

5/17/2017,

5/18/2017,

5/19/2017,

5/20/2017

];

Intersection:

LOAD Date as IntersectDate

Resident DateSourceTable1;

Inner Join

LOAD Date as IntersectDate

Resident DateSourceTable2;

Complement:

Load Date as ComplementDate

Resident DateSourceTable1

WHERE NOT Exists(IntersectDate,Date);

Concatenate

LOAD Date as ComplementDate

RESIDENT DateSourceTable2

WHERE NOT Exists(IntersectDate,Date);

René V.