topic Re: Using Field Values as a Table Column in QlikView

Using Field Values as a Table Column

Anonymous — Thu, 09 May 2013 17:10:42 GMT

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

Re: Using Field Values as a Table Column

mphekin12 — Thu, 09 May 2013 17:36:02 GMT

Will the attached sample app work?

Re: Using Field Values as a Table Column

calvindk — Thu, 09 May 2013 17:48:27 GMT

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?

Re: Using Field Values as a Table Column

Anonymous — Thu, 09 May 2013 18:01:08 GMT

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

];

Re: Using Field Values as a Table Column

Anonymous — Thu, 09 May 2013 18:16:05 GMT

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!

Re: Using Field Values as a Table Column

Thu, 09 May 2013 22:02:41 GMT

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

Gysbert_Wassenaar — Fri, 10 May 2013 14:36:31 GMT

See attached example.

Re: Using Field Values as a Table Column

Anonymous — Fri, 10 May 2013 14:42:33 GMT

Thank you, Gysbert! This is perfect!

Re: Using Field Values as a Table Column

mphekin12 — Fri, 10 May 2013 15:17:13 GMT

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.