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
Not applicable

selecting a field from another table

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

7 Replies
Not applicable

selecting a field from another table

hi,

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

Regards,

Lukas

aboumejjane
Not applicable

Re: selecting a field from another table

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

Not applicable

Re: selecting a field from another table

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

aboumejjane
Not applicable

Re: selecting a field from another table

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

aboumejjane
Not applicable

Re: selecting a field from another table

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 Smiley Frustrated. Do have an extra advice for me

Regards,

Isam

Not applicable

Re: selecting a field from another table

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 ;-)

aboumejjane
Not applicable

Re: selecting a field from another table

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