integrating field values in to 1 variable?

[Anonymous]

Hi Guys,

I need to calculate the transitetime between two different location. I have one field with "origin" and other with "destination".

I've created the following experession where it says that if the origin is 'x' and the destination is 'x' then the transittime should not be longer than 9 days:

if(Destination='DBX' and Origin='ATL' Or Destination='DBX' and Origin='ATW' Or Destination='DBX' and Origin='BDL' Or Destination='DBX' and Origin='BOS'  Or Destination='DBX' and Origin='HSV' Or Destination='DBX' and Origin='JFK' OR Destination='DBX' and Origin='LAX' Or Destination='DBX' and Origin='MIA' OR  Destination='DBX' and Origin='RDU' Or Destination='DBX' and Origin='SEA' Or Destination='DBX' and Origin='YMQ' Or Destination='DBX' and Origin='YUL' OR Destination='DBX' and Origin='YYZ',

    if(NetWorkDays(POD,DEP+1, 'D')<=9, 'OnTime', 'Late'),

this expression is way to long. I was thinking to use variables to solve this issue. I just don't know how.

Basically this expression should be like this:

if(Destination='DBX' and $(variable),

if(networkdays(enddate, startdate+1),'D')<=9, 'OnTime', 'Late') as status,

So my question is : how can I integrate or group all the destinations to 1 variable?

Hope u can help!

Cheers

iSam

[sridhar240784]

Sam,

not sure i have understood your question properly.

Check out this attached appliaction.

Your code should be some thing like this.

//Declare your variable with SET operator.

set vRegion =(Region= 'w' or Region= 'x' or Region= 'y' or Region= 'z' or Region= 's');

Load *,

if( Test = 'a' and $(vRegion) ,'OnTime','Late') as YES_NO_STATUS

;

LOAD * INLINE [

   Test,Region

    a, w

    a, x

    a, y

    a, z

    a, s

    a, d

    a, f

    a, g

    b, z

    b, s

    b, d

    b, f

    b, g

];

Hope this helps you.

-Sridhar

[rwunderlich]

Instead of OR use the match() function.

If(Destination='DBX' AND match(Origin, 'ATL'. ATW', 'BDL', 'BOS', etc...)

-Rob

http://robwunderlich.com

[Anonymous]

Hi Rob,

Thanks for your help! I’ve managed to use the match () function. I’m still an qlikview newbie so don’t know how to shorten my expression (lol):

Anyway, I now have the following expression,

if(Destination='BJS' and Match(Origin='ATL','ATW','BDL','BOS','HSV','JFK','LAX','MIA','RDU','SEA','YMQ','YUL','YYZ'),

if(NetWorkDays(POD,DEP+1)<=9, 'OnTime', 'Late'),

if(Destination='AMS' and Match(Origin='ATL','ATW','BDL','BOS','HSV','JFK','LAX','MIA','RDU','SEA','YMQ','YUL','YYZ'),

if(NetWorkDays(POD,DEP+1)<=9, 'OnTime', 'Late'),

if(Destination='IZM' and Origin='AMS',

if(NetWorkDays(POD,DEP+1)<=8, 'OnTime', 'Late'),

if(Destination='BJS' and Origin='AMS',

if(NetWorkDays(POD,DEP+1)<=11, 'OnTime', 'Late'),

if(Destination='IZM' and Match(Origin='ATL','ATW','BDL','BOS','HSV','JFK','LAX','MIA','RDU','SEA','YMQ','YUL','YYZ'),

if(NetWorkDays(POD,DEP+1)<=7, 'OnTime', 'Late'),

if(Origin='AMS' and Match(Destination='ATL','ATW','BDL','BOS','HSV','JFK','LAX','MIA','RDU','SEA','YMQ','YUL','YYZ'),

if(NetWorkDays(POD,DEP+1)<=7, 'OnTime', 'Late'))))))) as StatusX,

However my chart display’s 3 things: ‘OnTime’, ‘Late’ and ‘-‘, I think the last one is somehow related to a missing value.

These are the All destinations and origins below:

ORIGIN: DESTINATION:

AMS AMS

JFK BJS

LAX IZM

BJS

MIA

SEA

HSV

YYZ

ATL

ATW

BDL

BOS

RDU

YMQ

YUL

What could cause the ‘-‘ to appear and how can I solve it?

Thanks a lot for your help!

iSam

[rwunderlich · Accepted solution]

The - appears for missing and null. The null can come for an expression when it can't caclulate correctly. For example when you have more than one row and no aggregation expression. For

NetWorkDays(POD,DEP+1)

would fail if there was more than one POD.

That may help you, but as you've discovered it can be difficult to debug long if() expressions. Better to simplify in the script.

1. In the load, Create a new field "Route" which is the concatenation of Orgin & Destination.

Orgin & '|' & Destination AS Route

2. Load a table of limit days for each route.

Limits:

LOAD * INLINE [

Route, LimitDays

BJS|ATL, 9

BJS|ATW, 9

BJS|AMS, 11

];

3. Then your chart expression becomes simply:

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

-Rob

[Anonymous]

Hi Rob,

Sorry for my late response. Thank you very much for your help!!!!! I really appreciated it!

Creating an inline table did simplify my expression. I loaded my script and added the expression in my chart. Normally ‘OnTime’ and ‘Late’ should appear in the chart. However I only see ‘Late’

Basically I want to show the user how many shipments are on time and how many of them are late.

I selected two Dimensions:

1. Month

2. Status (calculated) =if(NetWorkDays(DEP,POD+1)<=LimitDays, 'OnTime', 'Late')

I then did the following in the expression tab:

Count( Distinct STT) ‘STT’ is like a track & trace number.

That gave me the following chart:

I’m missing ‘OnTime’.

What I’m a doing wrong?

My apologies for all my questions. And thanks for all your help!!!

iSam

[Anonymous]

Hi Rob,

Thanks for your help!!!! I solved it. I had to replace LimitDays by 'LimitDays'. That solved the problem. But still I appriciated for helping me to simplify the script.