Remove duplicate values based on different field

[CanOls]

Hi Qlik Community

I have a field called Institute with repeating values and a field terminate day.  Like this:

Institute               Terminate date

1 Science              -

1 Science              03/03-2018

2 Math                   -

3 Space                 -

3 Space                02/01/2020

etc...

My task is, how do I only show the institutes where there is either no terminate date at all, or where the terminate date is later than the current day.

I have tried a where clause in the load, but I can't quite get the syntax right. 

So, how do I remove all the instances of institute where there is no terminate date or the terminate date is later than the current day. 

Any help is appreciated

Casper

[trdandamudi]

One way is as below:

Data:
Load *
where Len(Trim([Terminate date])) > 0
and [Terminate date] < now();

Load * Inline [
Institute,Terminate date
1 Science,
1 Science,03/03/2018
2 Math,
3 Space,
3 Space,02/01/2020
];

Hope this helps..

[mr_janne]

Hello!

First of all your data seems to be non-uniformal:

Date format for "03/03-2018" is different than "02/01/2020".

But after you sort that out you could do something like this for load script:

Data:

load Institute,

[Terminate date]

from Data.txt

where date#([Terminate date],'DD/MM/YYYY')>date(today()) or not(isnull(date#([Terminate date],'DD/MM/YYYY')));