linking columns in two tables with formulas

[#1]

Hi,

I have a table with 42 patients, with longitudinal sampling. Each day has 6 SOFA score components and the total SOFA score (the sum of the components) (see attached long table). Now I want to get that info into my short table (attached) which only has patients, where I have a column of the average SOFA score per patient, average of the 1st 3 days (0 through 3) and average of the 1st 7 days (0 through 7). How can I do it so that the tables are linked: whenever a component of the SOFA score changes in the original long table, the averages in the short table change in result?

I was able to do this in a static fashion using table summary and selecting rows accordingly, and copy-pasting into the short table, but I'm not able to understand how the link tables functionality works.

thanks,

uriel.

[#2 · Accepted solution]

First I did the calculation in the Long table, using Col Mean function with by variable (Study ID).

e.g. for the 0..3 days average:

Col Mean( If( :Day <= 3, :total SOFA score, . ), :StudyID )

The Problem is, that Linking is not working from the short table to the Long, because in the Long table the Study ID is not unique.

A Workaround is to generate a new Link ID in both tables, in the Long table it May be:

Char( :StudyID ) || "_" || Char( :Day )

and in the short table:

Char(:StudyID) || "_0"

Then we can refer from the short table to the Long (Always day 0, but it contains the correct aggregated Information).

And now you have the Information in your short table, dynamically connected by symbolic link.

[#3]

now I got it - the problem was that linking doesn't work "one to many". The workaround is nice!

Thanks a lot!

Uriel.