# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for
Did you mean:
Creator

## Pick Date based on status

Hello - I have an example as below.

 ID date Type 1 7/8/2020 Plan 1 7/9/2020 Done 1 7/10/2020 Close 2 7/9/2020 Plan 2 7/10/2020 Future 2 7/11/2020 Close

Here I want to show 2 Dates (Now & Next)  for each ID.If there is Type called Done for each ID, (Ex ID 1)for NOW I want to show 7/9/2020 and Next would be 7/10/2020 and if there is no Done (Ex: ID 2)  then Now would be 7/9/2020 and Next would be 7/11/2020.

Thank you much🙏

Labels (6)

• ### vegar

1 Solution

Accepted Solutions

One solution is.

``````tab1:
ID, date, Type
1, 7/8/2020, Plan
1, 7/9/2020, Done
1, 7/10/2020, Close
2, 7/9/2020, Plan
2, 7/10/2020, Future
2, 7/11/2020, Close
];

Left Join(tab1)
LOAD ID, If(Index(Concat(DISTINCT Type),'Done'), 'Y', 'N') As Flag,
FirstSortedValue(date,date) As StDt, FirstSortedValue(date,-date) As EdDt
Resident tab1
Group By ID;

Left Join(tab1)
LOAD *, If(Flag='Y' And Type='Done', 'Now',
If(Flag='Y' And Peek(Type)='Done', 'Next',
If(Flag='N' And date=StDt,'Now',
If(Flag='N' And date=EdDt,'Next')
)
)
) As Status
Resident tab1;``````
3 Replies

One solution is.

``````tab1:
ID, date, Type
1, 7/8/2020, Plan
1, 7/9/2020, Done
1, 7/10/2020, Close
2, 7/9/2020, Plan
2, 7/10/2020, Future
2, 7/11/2020, Close
];

Left Join(tab1)
LOAD ID, If(Index(Concat(DISTINCT Type),'Done'), 'Y', 'N') As Flag,
FirstSortedValue(date,date) As StDt, FirstSortedValue(date,-date) As EdDt
Resident tab1
Group By ID;

Left Join(tab1)
LOAD *, If(Flag='Y' And Type='Done', 'Now',
If(Flag='Y' And Peek(Type)='Done', 'Next',
If(Flag='N' And date=StDt,'Now',
If(Flag='N' And date=EdDt,'Next')
)
)
) As Status
Resident tab1;``````

Output.

Creator
Author

Thank you Saran - That helped..I used the below expression in the front end..

If(Index(Concat(DISTINCT Status),'Done'),

FirstSortedValue({\$<Type={'Done'}>} Date, -Date),

If(not Index(Concat(DISTINCT Status),'Done'),

FirstSortedValue({\$<Type-={'Done'}>} Date, Date)

)
)

Tags
Community Browser