Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

kmcgovern1
Contributor

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

Re: Using Field Values as a Table Column

See attached example.


talk is cheap, supply exceeds demand
8 Replies
mphekin12
Valued Contributor

Re: Using Field Values as a Table Column

Will the attached sample app work?

calvindk
Contributor III

Re: Using Field Values as a Table Column

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?

kmcgovern1
Contributor

Re: Using Field Values as a Table Column

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

];

kmcgovern1
Contributor

Re: Using Field Values as a Table Column

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

Re: Using Field Values as a Table Column

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

Re: Using Field Values as a Table Column

See attached example.


talk is cheap, supply exceeds demand
kmcgovern1
Contributor

Re: Using Field Values as a Table Column

Thank you, Gysbert! This is perfect!

mphekin12
Valued Contributor

Re: Using Field Values as a Table Column

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.

Community Browser