Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Thanks
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.