OIS - MOSAIQ - re-ordering diagnosis

[AAM]

Thank you for previous help. I have hit a brick wall again!

The following is a view of a simple DB Query report :

which I run from our OIS database to produce the following output for a single patient who has had three disease defining events:

I want to rearrange this to produce a single line in order of dates that looks like this:

I can separate Diagnosis_Class = 1 from 5, but can’t see how to separate the Diagnosis_Class = 5 based on date.

Any help is greatly appreciated.

A

[ipazin]

Hi there @AAM,

if I got you right you can use Counter Generation to add unique id to every row. Then use Pivoting node where group column is PAT_ID1, pivot column is you newly added column and aggregation columns are MED_ID, Dx_Partial and Diagnosis_Class with aggregation first. To avoid getting awkward column names use following setting in Pivoting node:

Hope this helps! If any questions feel free to ask.

Br,
Ivan

[AAM]

Here is my workflow (the {Medical} table gives 42028 entries):

Here is the add Counter screenshot, showing that the unique counter has been added:

Here are three pivoting screenshots showing the configurations for group by PAT_ID1, pivot by counter and aggregation by MED_ID, Dx_Partial & Diagnosis_Class:

The output from the pivoting table has three parts:

1. Pivot table - this gives 26730 entries BUT there are 126085 columns of data! Three columns for each MED_ID by the look of it.
   
   
   
   
   
2. Group totals - this gives 26730 entries
   
   
3. Pivot totals -this gives one entry with 126085 columns of data.
   
   

I’ll do some playing. Any suggestions?

A

[ipazin]

Hi there @AAM,

I’m sry. I was only looking for one case. So you should add Rank node after Counter Generation node. There you define your Ranking Attribute as Counter column with Ascending option and your Group Attribute as PAT_ID1 column. Now in Pivoting node instead of Counter column use newly created rank column as pivoting column. This should hopefully work

Br,
Ivan

[AAM]

Dear Ivan,

Thank you for the modification. Being somewhat of an ‘IT-idiot’, I can appreciate the difference in your instructions though I would never have achieved this workflow.

It is working! I had to strip out almost all of the columns so that I could appreciate it working, but it is working. The items are listed in date sequence (earliest at the left) as expected.

Thanks for the help. Hopefully, solutions needing a pivot are now catered for.

A

[ipazin]

Hi @AAM,

glad you made it

Br,
Ivan