First Sorted Value

[Prabhu1204]

Hello,

I am really stuck on the  logic.. I tried first sorted value and it does seems to work.. for some ID the value is blank

I want to get the latest status for the each ID, 

Logic for fetching the latest status is : Firstly it has to take the maximum end date and then Maximum task order ( there might be the cases where the maximum end date can be more than 1 ). Please find below the sample input and output.

Sample Input
ID	Status	Task	End Date	Phase	Task Order
A123	Competed	1	7/29/2022	Review	1
A123	Competed	2	7/30/2022	Approval	2
B123	Completed	3	6/29/2022	Review	1
B123	Completed	4	6/30/2022	Approval	2
B123	Completed	5	7/1/2022	Finalize	3
B123	Completed	6	7/2/2022	Close	4
C123	Completed	7	5/29/2022	Review	1
C123	Completed	7	5/31/2022	Approval	3
C123	Completed	7	5/31/2022	Close	4
Expected Output
ID	Status	Task	End Date	Phase	Task Order	Latest status
A123	Competed	2	7/30/2022	Approval	2	Approval
B123	Completed	6	7/2/2022	Close	4	Close
C123	Completed	7	5/31/2022	Close	4	Close

[Prabhu1204]

@jagan  could you please help

[MayilVahanan]

Hi 

I think, u can use Task Order alone

FirstSortedValue(Phase, -[Task Order])

[Prabhu1204]

Hello @MayilVahanan ,

Thanks for your reply..  I did try that and that not seems to be working.. for some I'd the latest phase shows null value.. Also the task order  Is an inline table created for task sequences.

[sidhiq91 · Accepted solution]

@Prabhu1204  I hope you don't have any restrictions that you need to get the desired output only though the Firstsortedvalue() function. Because I have tried the same logic in the Script Editor, please see the script below.Also I have attached the Screenshot.

NoConcatenate
Temp:
Load ID,
Status,
Task,
Date(Date#([End Date],'MM/DD/YYYY'),'MM/DD/YYYY') as [End Date],
Phase,
[Task Order]
Inline [
ID, Status, Task, End Date, Phase, Task Order
A123, Completed, 1, 7/29/2022, Review, 1
A123, Completed, 2, 7/30/2022, Approval, 2
B123, Completed, 3, 6/29/2022, Review, 1
B123, Completed, 4, 6/30/2022, Approval, 2
B123, Completed, 5, 7/1/2022, Finalize, 3
B123, Completed, 6, 7/2/2022, Close, 4
C123, Completed, 7, 5/29/2022, Review, 1
C123, Completed, 7, 5/31/2022, Approval, 3
C123, Completed, 7, 5/31/2022, Close, 4
];

NoConcatenate
Temp1:
Load *,
ID&'-'&[End Date]&'-'&[Task Order] as AutoID

Resident Temp;
Drop table Temp;

Inner join (Temp1)
Temp2:
Load ID&'-'&Max_End_date&'-'&Max_Task_Order as AutoID,
Max_End_date,
Max_Task_Order;

Load ID,
Date(Max([End Date]),'MM/DD/YYYY') as Max_End_date,
Max([Task Order]) as Max_Task_Order
Resident Temp1
group by ID;

Drop Fields [End Date],[Task Order] from Temp1;

Exit Script;

Please let me know if this has resolved your issue.

[Prabhu1204]

Hi.. Yes This will work.. I tried this as well. but I want a dedicated field name as Last Status, the dimension will be used in different graphs as well..

[sidhiq91]

@Prabhu1204  Could you please tell me on what basis are you getting the Last Status?

[Prabhu1204]

@sidhiq91  The maximum date of contract and then the maximum task order = Stauts as last stats

THe logic you had mentioned will work. But as I said i need a dimension for the last status that will be used in other bar chart. 

[sidhiq91]

@Prabhu1204  I see the Last status is same as the Phase Dimension.  You can Either rename the Phase as Last Status or create new field like Phase as Last_Status. Please correct me in case if I am not able to understand it correctly.

[Prabhu1204]

No. The thing is like I can rename the field as last dim that is fine. But since in the table we are havng  max_order and max_end date the table wil shows up status dim as expected. But in the bar i will need to have last phase and count of id as measure.. If is add max task and max end date in expression using group it is not showing correctly. I might be wrong some where. But I had a  good clue from your approach.