Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi :
I want to load data only exists in colomn1 but not exists in colimn2
my example is :
ID Date1 Date2
1 06/07/2018 01/01/2018
2 06/08/2018 02/02/2018
3 13/01/2012 13/09/2011
4 13/09/2011 30/05/2020
5 30/05/2020 13/01/2012
so I only want to load the data exists in Date2 , but not exists in Date1
should be like :
ID Date1 Date2
1 06/07/2018 01/01/2018
2 06/08/2018 02/02/2018
You can load the Date2 column first as a lookup table like this and then use that to filter what is loaded:
LOOKUP_DATE2:
LOAD DISTINCT
Date2 AS Lookup.Date2
FROM
<.....>;
RESULT_TABLE:
LOAD
*
FROM
<.....>
WHERE
Not(Exists(Lookup.Date2,Date1));
DROP TABLE LOOKUP.DATE2;
Another way which I thought
T1temp:
LOAD *
INLINE [
id, Date1, Date2
1, 06/07/2018, 01/01/2018
2, 06/07/2018, 02/02/2018
3, 13/01/2012, 13/09/2011
4, 13/09/2011, 30/05/2020
5, 30/05/2020, 13/01/2012
];
T2:
LOAD id as idtemp,Date1
Resident T1temp;
inner join(T2)
LOAD Date2 as Date1 Resident T1temp;
T1final:
LOAD Date1, Date2,RowNo() as rn resident T1temp
where not exists(idtemp,id);
Drop Tables T1temp,T2;
Drop Field rn;
When applicable please mark the appropriate replies as CORRECT https://community.qlik.com/docs/DOC-14806. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.