Simple data with little complex logic - need help

Wed, 16 Aug 2017 19:19:49 GMT

Hello,

I need some help/suggestion on the scenario I have below, here is the raw data from the table :

ID	Sub ID	Date	Status
123	111	1/1/16	Open
123	222	2/1/16	closed
123	333	3/2/16	Assinged
123	444	1/1/17	New
123	777	2/1/17	Accepted
456	888	1/2/16	New
456	999	2/2/16	Closed
456	121	1/1/17	New
456	123	3/3/17	Open

I want to show above data in below way in Qlik table :

- I need to show Record for the ID which has most recent date in this year and also past year, only two rows per ID

- if the status is "Closed" for any of the SubID for the ID in that year show closed instead of whatever status value is for that record, is none closed then show whatever value is there for the status for the record.

here is example output :

ID	Sub ID	Date	Status
123	333	3/2/16	closed
123	777	2/1/17	Accepted
456	999	2/2/16	closed
456	121	3/3/17	Open

please help how to get this output.

topic Simple data with little complex logic - need help in QlikView

Simple data with little complex logic - need help