Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
CanOls
Contributor III
Contributor III

Remove duplicate values based on different field

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

 

Labels (2)
2 Replies
trdandamudi
Master II
Master II

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

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')));