Using Field Values as a Table Column

[Anonymous]

I'm loading approval records from a DB that have an ever changing set of approvers associated with each record.  The data is stored in the following format (sample data being loaded inline but column format the same).

Approvals:
LOAD * Inline [
ID, Desc, Name, Status
5, Test 1, Joe, Approved
5, Test 1, Matt, Approved
5, Test 1, John, Current
5, Test 1, Bill, Future
5, Test 1, Todd, Future
20, Test 2, Harry, Approved
20, Test 2, Joe, Current
20, Test 2, Matt, Future
20, Test 2, Bill, Future
];

Once the data is loaded, the requirement is that it is displayed in the following format, where the approvers shown are a dynamic set that are selected from the available approvers.  Does anyone have any ideas on how to do this?  Pivot tables come close when I move the Name column to the top but display multiple lines for each ID where I just want a single line.  I appreciate your help in advance.

ID	Desc	Joe	Matt	John
5	Test 1	Approved	Approved	Current
20	Test 2	Current	Future	N/A

[mphekin12]

Will the attached sample app work?

[calvindk]

If you dont show multiple lines for each ID, which ID, Desc and status would you then want to show??

Could you show us the exact desired output?

[Anonymous]

Thanks for your help!  Yeah, that had been what I was trying but I'm still running into a problem.  It seems like it gets thrown out of whack if one approver has multiple status' on a single ID.  For example, if Joe in the example below has approved something but is now up again to approve, he doesn't show up in your table.  I know it may not make sense as far as why someone would need to approve twice but unfortunately the system is designed that way.  In this scenario we would want the data with the highest approval order.  I apologize for missing this scenario in the previous example.  Thanks again for your help!

Approvals:
LOAD * Inline [
ID, Desc, Name, Status, Order
5, Test 1, Joe, Approved, 1
5, Test 1, Matt, Approved, 2
5, Test 1, John, Current, 3
5, Test 1, Bill, Future, 4
5, Test 1, Todd, Future, 5
20, Test 2, Joe, Approved, 1
20, Test 2, Harry, Approved, 2
20, Test 2, Joe, Current, 3
20, Test 2, Matt, Future, 4
20, Test 2, Bill, Future, 5
];

[Anonymous]

Anders, the output above is exactly what I'm looking for.  Each ID will have it's own line and the Desc is 1:1 with the ID so that will be displayed on the same line.  As for status, the status for each approver should be displayed in that person's corresponding column.  As described in my followup, in the scenarios where there are multiple lines for a status, the status with the highest Order should be shown.  The output would be exactly what is shown below except, for ID 20, Joe's column should display "Current."  Thank you for your help!

[#6]

Hi Kevin

I think this will do what you need. The Tmp is only bringing the lines with Max Order for that group which is then inner joined to main table so you get only the rows you want.

Something like:

Tmp:

Inner Join
Load
ID, Name,
Max(Order) as Order
Resident Approvals
Group by ID, Name
;

Bruce

[Gysbert_Wassenaar · Accepted solution]

See attached example.

[Anonymous]

Thank you, Gysbert! This is perfect!

[mphekin12]

Gysbert,

Would you be able to explain the formula you used?  I think it would be very helpful!  Especially the following section:

{<Order={'=aggr(min(Order),ID,Status)'}>}

Thank you.