Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
I have 2 tables:
TransitTimes: //table 1
ID,
TransitTime
Data: //table2
DateReceived,
DateArrived
if(networkdays(DateReceived,DateArrived)<=TransitTime, 'OnTime', 'Late') as Status // this offcourse won't work, since the field "TransitTime" does not exits in the table 'Data'.
How could I create the field 'Status' from the 2 tables above?
Thanks in advance!
Cheers,
iSam
So, if table1 only has two fields, field1 ID and field2 TransitTime, you could use a mapping load with applymap.
TransitTimes:
MAPPING LOAD
ID,
TransitTime
from ....;
Data:
LOAD
ID,
DateReceived,
DateArrived,
if(networkdays(DateReceived,DateArrived)<=applymap('TransitTimes',ID), 'OnTime', 'Late') as Status
from ...;
Instead of the Mapping table and applymap, you could also use Lookup() function.
Hope this helps,
Stefan
iSam,
the two tables are not linked to each other in any way? No ID in table2? So how would you relate these records in any way?
Sorry Stefan your right,
The Data table contains the ID table as well
TransitTimes: //table 1
ID,
TransitTime
Data: //table2
DateReceived,
DateArrived,
ID
Thanks in advance!!
iSam
So, if table1 only has two fields, field1 ID and field2 TransitTime, you could use a mapping load with applymap.
TransitTimes:
MAPPING LOAD
ID,
TransitTime
from ....;
Data:
LOAD
ID,
DateReceived,
DateArrived,
if(networkdays(DateReceived,DateArrived)<=applymap('TransitTimes',ID), 'OnTime', 'Late') as Status
from ...;
Instead of the Mapping table and applymap, you could also use Lookup() function.
Hope this helps,
Stefan
Lookup function:
Load table1 just as before, no mapping prefix.
Then
Data:
LOAD
ID,
DateReceived,
DateArrived,
if(networkdays(DateReceived,DateArrived) <= lookup('TransitTime', 'ID', ID, 'TransitTimes'),'OnTime','Late') as Status
from...
another option is joining your tables
TransitTimes:
load ID, TransitTime
Join (TransitTimes)
load ID,DateReceived, DateArrived
join Load ID, if(networkdays(DateReceived,DateArrived)<=TransitTime, 'OnTime', 'Late') as Status Resident TransitTimes;
Hi Stefan,
I used the lookup function. It did the job!! Thanks for all your help!!!!
iSam