How do I calculate Age in the load using data from two tables?

[#1]

Hi everyone -

I need to calculate the age using BIRTH_DATE from the Demographics table I'm loading and then use ENCOUNTER_DATE from the Procedures table. How do I  do this in the Load Script? I would like to know how old the patient is at the time of each procedure is done.

Thank you,

Karen

[Miguel_Angel_Baeyens · Accepted solution]

Hello Karen,

With no better knowledge of your data model I'll assume you have a Patient_ID in both Demographics and Procedures table. If this is the case, I'd use a Mapping table based on Demographics and applied to the Procedures table, using Patient_ID as main parameter. Basically, it will look like the following:

Demographics:
LOAD *
FROM Demographics.Source;
PatientBirthdateMap:
MAPPING LOAD Patient_ID,
      Birth_Date
FROM Demographics.Source;
Procedures:
LOAD *,
     Age(Encounter_Date, ApplyMap('PatientBirthdateMap', Patient_ID)) AS Patient_Age
FROM Procedures.Source;

Take special care on the dates formats, the more similar they are the lesser problems you will encounter.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[prieper]

Guess the common field in both tables will be the name of the patient, thus tables would be linked like

Demographic: LOAD Name, Birthday .... FROM .....;

Encounter: LOAD Name, EncounterDate, .... FROM ...;

Thus as expression in your tables etc you may use EncounterDate - Birthday in order to calculate the birth.

If you need to calculate this in your script, you need to JOIN the tables first and then add as new field something like

EncounterDate - Birthday AS Age.

HTH
Peter

[#4]

Thank you both.  Your suggestions and example we great and really helped.  I was able to get it figured out.

Thank you again!

Karen