Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

xz103389
New Contributor

Qlik Sense, load data only exists in colomn1 but not exists in colimn2

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

3 Replies
MVP
MVP

Re: Qlik Sense, load data only exists in colomn1 but not exists in colimn2

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;

ujjwalraja
New Contributor III

Re: Qlik Sense, load data only exists in colomn1 but not exists in colimn2

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;

Capture.PNG

pablolabbe
Valued Contributor II

Re: Qlik Sense, load data only exists in colomn1 but not exists in colimn2

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.