Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Filtering dates in qlikview

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/NFieldDate
1AAA12/02/2015
1BBB12/02/2015
1CCC25/05/2015
1DDD25/02/2015
2AAA30/03/2015

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

S/NFieldDate
1CCC25/02/2015
1DDD25/02/2015
2AAA30/03/2015

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

Thank you

5 Replies
trdandamudi
Master II
Master II

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

Anonymous
Not applicable
Author

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.

maxgro
MVP
MVP

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

1.png


Anonymous
Not applicable
Author

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

MarcoWedel

Hi,

another solution might be:

QlikCommunity_Thread_218176_Pic1.JPG

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