Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Using Field Values as a Table Column

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.

IDDescJoeMattJohn
5Test 1ApprovedApprovedCurrent
20Test 2CurrentFutureN/A
1 Solution

Accepted Solutions
Gysbert_Wassenaar

See attached example.


talk is cheap, supply exceeds demand

View solution in original post

8 Replies
mphekin12
Specialist
Specialist

Will the attached sample app work?

calvindk
Creator III
Creator III

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
Not applicable
Author

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
Not applicable
Author

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!

Not applicable
Author

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

See attached example.


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

Thank you, Gysbert! This is perfect!

mphekin12
Specialist
Specialist

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.