Qlik Community

Ask a Question

New to Qlik Sense

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

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
apthansh
Creator
Creator

Pick Date based on status

Hello - I have an example as below.

IDdateType
17/8/2020Plan
17/9/2020Done
17/10/2020Close
27/9/2020Plan
27/10/2020Future
27/11/2020Close

 

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🙏

 

1 Solution

Accepted Solutions
saran7de
Master
Master

One solution is.

tab1:
LOAD * INLINE [
    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;

View solution in original post

3 Replies
saran7de
Master
Master

One solution is.

tab1:
LOAD * INLINE [
    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;

View solution in original post

saran7de
Master
Master

Output.

commQV41.PNG

apthansh
Creator
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)


)
)