Resident table?

[bobbydave]

I have two separate tables with no association.

TableA:

Load

     INCIDENT_YEAR

from incidentdate.xlsx

TableB:

Load

    BIRTH_YEAR

from birthdate.xlsx

I can create an expression to say INCIDENT_DATE - BIRTHDATE but what I actually want to do is to have this done in the script.

Can I use a resident table to do this? I am not too familiar with subtracting from two different tables in the script. I just want to have the result and use that then. Do I need to do a join or something to get the result?

[sunny_talwar]

You either need to join or use some technique (such as ApplyMap, LookUp,...) to bring them within one table to perform any calculations which involve both these two fields. If they sit in two different tables, back end calculation involving both the two fields isn't going to work.

HTH

[vinieme12]

if there is nothing to associate the fields of data, then how would you determine what Incident Year should be subtracted with what Birth Year? or do you want all Incidentyears to be subtracted with all birth year!!

[Mark_Little]

Hi,

Like suggested they need to be in the same table to be subtracted. if you are currently doing this in the front end there must be a link or key between the to tables?

So with either a join or applymap you would be able to get the two field in the same table and then with a resident load or preceding load perform your calculation.

Mark

[#5 · Accepted solution]

Here:

TableA:
LOAD
  2000 + RowNo() as INCIDENT_YEAR
AutoGenerate 100;

TableB:
Load
    1900 + RowNo() as BIRTH_YEAR
AutoGenerate 100;

IncidentVsBirth:
NoConcatenate
LOAD
  *
Resident TableA;

Join (IncidentVsBirth)
LOAD
  *
Resident TableB;

Left Join (IncidentVsBirth)
LOAD
  *
  , INCIDENT_YEAR - BIRTH_YEAR as YearDiff
Resident IncidentVsBirth;

You can expect right answer for right question only.

[bobbydave]

Yes, all birthdates would be subtracted from all incident years.

If you can imagine 20 people born between 1920 and 2015. Each has an accident and the year that accident happens needs to be subtracted from the year the incident happened to show their age at that point in time.