OIS - MOSAIQ - numbering recurrences

I have returned! A little wiser, but not wise enough to not need to seek help again. KNIME is fabulous, unlike this user!

I have a table called {Medical}. It contains each new diagnosis/stage entry has a Diag_Code (the site, e.g., 'prostate, ‘breast’), a sequential MED_ID and a Diagnosis_Class=1 and a DxDate.

It also contains each recurrence diagnosis/stage entry which also has a Related_MED_ID (the value of the MED_ID of the original diagnosis/stage entry), a DxDate, and a Diagnosis_Class NOT 1 (3, 5, 7 for example).

Patients can have 0, 1, 2, 3, 4, …n recurrences (all are Diagnosis_Class NOT 1) which can only ranked by Dx_Date (first recurrence has an earlier diagnosis than the second recurrence which is earlier than the third, etc; the Related_MED_ID will not work reliably for this).

  1. I want to pull out the original diagnosis
    (I have done this by pulling entries from {Medical} with Diagnosis_Class = 1).

  2. I want to pull out the first recurrence diagnosis
    (I have done this by pulling entries from {Medical} with NOT Diagnosis_Class = 1, and then GroupBy Related_Med and Diag_Code, with minimum(Dx_Date) in the manual aggregation tab).

  3. I want to pull out the second recurrence diagnosis … now I am stuck!
    I can’t find a way to identify the second recurrence reliably. I can select the first using the minimum(Dx_Date) in the manual aggregation tab, and the last using the maximum(Dx_Date) in the manual aggregation tab. I could assign a position to the last recurrence by using a group count (if there are four recurrences then the maximum date one must be #4), but #2 and #3 are defeating me.

My question is: if I have the original database dump listing all of the recurrences (a unique MED_ID, a Related_MED_ID (that groups the recurrences), and a Dx_Date (that orders the recurrences), how do I add a new column into which goes a number indicating the temporal position (earliest to latest) of the recurrence within the Related_MED_ID?

It should end up like this:

I am grateful for previous help.

A

I believe that I have solved this issue! Thank you for the help.

I used the Pivot reply from previously with a few tweaks to get it right.

KNIME_Diagnosis_PIVOT.knwf (26.8 KB)

2 Likes

Hi @AAM,

seems you are wise enough for this one :wink:

Br,
Ivan

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.