Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
hi,
if the tables have an relation you can map the field and than you can make your if-statement.
Regards,
Lukas
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
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
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
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
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
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 😉
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