topic Re: Filtering dates in qlikview in QlikView

Filtering dates in qlikview

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I trying to get the most actualized results from a table in load script by a date field and drop the outdated ones.

S/N	Field	Date
1	AAA	12/02/2015
1	BBB	12/02/2015
1	CCC	25/05/2015
1	DDD	25/02/2015
2	AAA	30/03/2015

In this case I want to get only the last two rows.

S/N	Field	Date
1	CCC	25/02/2015
1	DDD	25/02/2015
2	AAA	30/03/2015

I think is something I can do with the S/N and Date. Does anyone have an idea?

Thank you

Re: Filtering dates in qlikview

trdandamudi — Tue, 24 May 2016 18:59:30 GMT

What is the reason BBB is not part of the final output ?

Re: Filtering dates in qlikview

Anonymous — Tue, 24 May 2016 19:04:43 GMT

The date for AAA and BBB is older than DDD and they have the same S/N. So in this case I get the most update ones.

If I have the same S/N and same Date I keep both.

Re: Filtering dates in qlikview

maxgro — Tue, 24 May 2016 19:05:48 GMT

In script

Source:

LOAD [S/N],

Field,

Date

FROM

[https://community.qlik.com/thread/218176]

(html, codepage is 1252, embedded labels, table is @1);

Final:

NoConcatenate

LOAD *

Resident Source

Where [S/N] <> Peek([S/N])

Order by [S/N], Date desc;

DROP Table Source;

Result

Re: Filtering dates in qlikview

Anonymous — Tue, 24 May 2016 19:35:27 GMT

Sorry for not being clear while explaining.

In this case it only gets one value per S/N, but i have to keep values with duplicated dates like new example below:

S/N Field Date

1 CCC 25/02/2015

1 DDD 25/02/2015
2 AAA 30/03/2015

Thanks

Re: Filtering dates in qlikview

MarcoWedel — Tue, 24 May 2016 21:26:57 GMT

Hi,

another solution might be:

table1:
LOAD * FROM [https://community.qlik.com/thread/218176] (html, codepage is 1252, embedded labels, table is @1);

Right Join
LOAD [S/N],
    Max(Date) as Date
Resident table1
Group By [S/N];

(If you correct the Date typo in your example table from 25/05/2015 to 25/02/2015 you should get the expected result )

hope this helps

regards

Marco