topic selecting a field from another table in QlikView

selecting a field from another table

Anonymous — Mon, 26 Sep 2011 08:55:42 GMT

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

selecting a field from another table

Mon, 26 Sep 2011 09:25:23 GMT

hi,

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

Regards,

Lukas

Re: selecting a field from another table

Anonymous — Mon, 26 Sep 2011 09:58:06 GMT

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

Re: selecting a field from another table

Mon, 26 Sep 2011 10:07:50 GMT

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

Re: selecting a field from another table

Anonymous — Mon, 26 Sep 2011 10:56:51 GMT

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

Re: selecting a field from another table

Anonymous — Mon, 26 Sep 2011 11:26:54 GMT

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

Re: selecting a field from another table

Mon, 26 Sep 2011 11:33:17 GMT

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 😉

Re: selecting a field from another table

Anonymous — Mon, 26 Sep 2011 13:23:04 GMT

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