one field from 2 tables

[Anonymous]

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

[swuehl]

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?

[Anonymous]

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

[swuehl · Accepted solution]

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

[swuehl]

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...

[#6]

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;

[Anonymous]

Hi Stefan,

I used the lookup function. It did the job!! Thanks for all your help!!!!

iSam