Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
What is the reason BBB is not part of the final output ?
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.
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
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
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