Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
NEW webinar Dec. 7th: 2023 Outlook, A Pivotal Year for Data Integration SIGN ME UP!
cancel
Showing results for 
Search instead for 
Did you mean: 
xz103389
Contributor
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
petter
Partner - Champion III
Partner - Champion III

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
Contributor III
Contributor III

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
Luminary Alumni
Luminary Alumni

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.