Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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
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!!
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
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.
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.