topic Re: First Sorted Value in QlikView

First Sorted Value

Prabhu1204 — Sat, 30 Jul 2022 14:55:40 GMT

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

Re: First Sorted Value

Prabhu1204 — Sun, 31 Jul 2022 04:32:22 GMT

@jagan could you please help

Re: First Sorted Value

MayilVahanan — Sun, 31 Jul 2022 07:51:09 GMT

I think, u can use Task Order alone

FirstSortedValue(Phase, -[Task Order])

Re: First Sorted Value

Prabhu1204 — Sun, 31 Jul 2022 08:11:45 GMT

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.

Re: First Sorted Value

sidhiq91 — Sun, 31 Jul 2022 08:16:44 GMT

@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.

Re: First Sorted Value

Prabhu1204 — Sun, 31 Jul 2022 11:56:23 GMT

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..

Re: First Sorted Value

sidhiq91 — Sun, 31 Jul 2022 16:59:15 GMT

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

Re: First Sorted Value

Prabhu1204 — Mon, 01 Aug 2022 03:49:13 GMT

@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.

Re: First Sorted Value

sidhiq91 — Mon, 01 Aug 2022 05:27:07 GMT

@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.

Re: First Sorted Value

Prabhu1204 — Mon, 01 Aug 2022 09:44:39 GMT

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.

Re: First Sorted Value

Prabhu1204 — Mon, 01 Aug 2022 09:45:20 GMT

if you can tel me the expression to be used in set analysis i would try that