Question

Pivot table and aggregate based on a field

  • 20 March 2018
  • 4 replies
  • 13 views
S
Badge

I am pivoting a table based on an ID field so that all diagnosis codes are pivoted and grouped based on the ID. Here is an example of source and final tables I am trying to acheive. What transformers will be appropriate to get this done?

4 replies

stalknecht
Rank
Userlevel 1
Badge +18

I think that you can use an AttributePivoter to get the desired result.

S
Badge

This is where I am confused with the pivoter: I selected patientID to group rows by but for columns I want to select multiple columns to pivot under a single column named "Diagnosis Codes". I do not see the option to select multiple columns.

stalknecht
Rank
Userlevel 1
Badge +18

This is where I am confused with the pivoter: I selected patientID to group rows by but for columns I want to select multiple columns to pivot under a single column named "Diagnosis Codes". I do not see the option to select multiple columns.

Depending on the datasource you can use a sqlCreator to get an union select query or you can use the inlineQuerier to get the desired result.

 

See attached template

 

demo.fmwt

 

 

S
Badge
Depending on the datasource you can use a sqlCreator to get an union select query or you can use the inlineQuerier to get the desired result.

 

See attached template

 

demo.fmwt

 

 

@stalknecht, thanks for your comment. That is exactly what I ended up doing. I am using SQLCreator with Unpivot sql query to transpose from columns to rows.

 

Reply


Community Stats

28,706
Posts
109,463
Replies
36,551
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.