Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 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.
@jagan could you please help
Hi
I think, u can use Task Order alone
FirstSortedValue(Phase, -[Task Order])
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.
@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.
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..
@Prabhu1204 Could you please tell me on what basis are you getting the Last Status?
@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.
@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.
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.