topic Re: Remove duplicate values based on different field in QlikView

Remove duplicate values based on different field

CanOls — Sat, 16 Nov 2024 02:41:07 GMT

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

Re: Remove duplicate values based on different field

trdandamudi — Wed, 21 Aug 2019 10:52:56 GMT

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..

Re: Remove duplicate values based on different field

mr_janne — Wed, 21 Aug 2019 10:56:28 GMT

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