We have a dataset for folks (person_id) with certain conditions (condition_name). There is a row for every condition they have. We need to be able to pivot each of these conditions into their own columns by person_id so that we have 1 row per person, with a column for each condition they have.
We've used several table calculations, and we are appropriately ranking the conditions, but we can't get that condition field to pivot into unique columns by condition on a single row. Here is what we have tried so far.
Partition function to get dimension we want to partition by: match(${table.person_id}, ${table.person_id})
Rank function to get rank inside each patient partition: if(${partition}=offset(${partition},-1),1+row ()-${partition},1)
We only want to see the top five conditions, so we created this table calculation: ${rank_of_partition} <= 5
We then hid the "No" values in the table.
From there, we don't know how to pivot the condition field by the person_id to have one column per condition.
Any suggestions?
LinkedIn
Twitter
