Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

aboumejjane
Contributor

one field from 2 tables

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

1 Solution

Accepted Solutions
MVP
MVP

one field from 2 tables

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

6 Replies
MVP
MVP

one field from 2 tables

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?

aboumejjane
Contributor

one field from 2 tables

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

MVP
MVP

one field from 2 tables

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

MVP
MVP

one field from 2 tables

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

Not applicable

one field from 2 tables

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;

aboumejjane
Contributor

one field from 2 tables

Hi Stefan,

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

iSam