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.
