topic one field from 2 tables in QlikView

one field from 2 tables

Anonymous — Fri, 02 Dec 2011 21:23:35 GMT

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

one field from 2 tables

swuehl — Fri, 02 Dec 2011 21:41:07 GMT

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?

one field from 2 tables

Anonymous — Fri, 02 Dec 2011 22:01:26 GMT

Sorry Stefan your right,

The Data table contains the ID table as well

TransitTimes: //table 1

ID,

TransitTime

Data: //table2

DateReceived,

DateArrived,

Thanks in advance!!

iSam

one field from 2 tables

swuehl — Fri, 02 Dec 2011 22:08:32 GMT

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

one field from 2 tables

swuehl — Fri, 02 Dec 2011 22:12:40 GMT

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

one field from 2 tables

Fri, 02 Dec 2011 22:19:02 GMT

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;

one field from 2 tables

Anonymous — Fri, 02 Dec 2011 22:50:19 GMT

Hi Stefan,

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

iSam