selecting a field from another table

[Anonymous]

Hi Guys,

I have a two tables "Data" and "Route": One table contains data with all the shipment info, the other table "Route" is a inline table that I created.

The first table does not contain the field "LimitDays". However the second (inline) Table has the field "LimitDays". The field limitdays contains the maximum days a shipment should not exceed.

Now, I want to calculate the transittime and therefore created the following expression in the frist table (Data):

if(networkdays(departure, arrival)<=LimitDays, 'OnTime', 'Late') as Status,

Because the field LimitDays does not exits in the first table, I get an error message from qlikview (which ofcourse makes sence) .

My question however is, how can I 'tell' qlikview to look for the limitdays in the second table (Route)? Is this btw possible?

Thanks in advance!!!!

iSam

[#2]

hi,

if the tables have an relation you can map the field and than you can make your if-statement.

Regards,

Lukas

[Anonymous]

Hi Lucas,

Thanks for your help! I’m still a newbie, so I’m sorry for my ignorance. I’ve looked up the map function. According to the Qlikview reference: I should place ‘mapping’ before my load and . Scripts is now:

Data:

Mapping LOAD

as STT,

HAWB,

Origin,

Destination,

Origin & '|' & Destination as Route,

num#(, '####.#') as GrossWeight,

num#(, '##.#') as TotalVolume,

Date(DEP) as DEP,

Month(DEP) as Month,

if(NetWorkDays(DEP,POD+1)<=LimitDays, 'OnTime', 'Late') as StatusTest,

FROM

(ooxml, embedded labels, table is DATA);

mapping select FieldDays from Route; //(that didn’t work) so I tried ‘Map LimitDays using route;’ but that didn’t work either. L

Route:

mapping LOAD * INLINE [

Route, LimitDays

DBX | ATW, 7

];

That am I doing wrong?

Thanks!

iSam

[#4]

You did some mistakes.Here it is 😉

Route:

LOAD * INLINE [

    Route, LimitDays

    DBX | ATW, 7

];

Route_Mapping:

Mapping LOAD Route,

                          LimitDays

Resident Route;            

Data:

LOAD

           if(NetWorkDays(DEP,POD+1)<=LimitDays, 'OnTime', 'Late') as StatusTest, 

          *

          ;

LOAD 

    Re: selecting a field from another table as STT,

     HAWB,

     Origin,

     Destination,

     Origin & '|' & Destination as Route, 

     num#(Re: selecting a field from another table, '####.#') as GrossWeight,

     num#(Re: selecting a field from another table, '##.#')  as TotalVolume,

     Date(DEP) as DEP,

     Month(DEP) as Month,

     applymap ('Route_Mapping',Origin & '|' & Destination,'n\a') AS LimitDays

FROM

Re: selecting a field from another table

(ooxml, embedded labels, table is DATA);

  Hope it helps

[Anonymous]

Hi Lukas,

Thanks for your help!!!  Unfortunately I still get the error that the field was not found.

I can't seem to find the mistake. I've attached my script, maybe you can find it:

As i'm still learning i'm curious why you Loaded the below expression separately:

Load

if(NetWorkDays(DEP,POD+1)<=LimitDays, 'OnTime', 'Late') as StatusTest,

*;

You did the following for the applymapfunction: applymap('Route_Mapping', Route, 'n/a') AS LimitDays. What does the n/a stand for and why is it under quote?

Thanks for all your help!!!

iSam

[Anonymous]

Hi Lucas,

I’m one step further. I solved the problem by removing: if(networkdays(DEP,POD)<=LimitDays, ‘OnTime’, ‘Late’) as StatusTest, from the datafield. And I’ve put route under quote.

There “just” one last problem and that is that by mapping this I have a syntactic key. Now, If I change the name of “Route” there would not be a relation between the 2 tables. If I change LimitDays the mapping won’t work :S. Do have an extra advice for me

Regards,

Isam

[#7]

Load

if(NetWorkDays(DEP,POD+1)<=Data.LimitDays, 'OnTime', 'Late') as StatusTest,

*;

Data:

LOAD

    [Tracking Number] as STT,

    HAWB,

    Origin,

    Destination,

    Origin & '|' & Destination as Route, 

    num#([Gross Weight], '####.#') as GrossWeight,

    num#([Total Volume], '##.#')  as TotalVolume,

    Date(DEP) as DEP,

    Month(DEP) as Month,

    if(NetWorkDays(DEP,POD+1)<=LimitDays, 'OnTime', 'Late') as StatusTest,

    ApplyMap('Route_Mapping',Origin & '|' & Destination, 'n/a') as Data.LimitDays,

    'Q' & Ceil(Month(DEP)/3) as Quarter,

    Date(DayStart(DEP)) as Date,

    Date(POD) as POD, 

    Date(ARR) as ARR,

    Date(CCD) as CCD

FROM

[..\Cis FokkerElmo.xlsx]

(ooxml, embedded labels, table is DATA);

this could be work 😉

[Anonymous]

Hi Lucas,

Thanks for your help! I really appreciated it! It works but not completely, it’s getting to complex for me, for I’m just a newbie. L

So I’m going to try something else. But again Thank u for all your help!!

Regards,

isam