6 Replies Latest reply: Sep 26, 2011 9:30 AM by Aissam Boumejjane RSS

    integrating field values in to 1 variable?

    Aissam Boumejjane

      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

        • Re: integrating field values in to 1 variable?
          Sridhar Ethiraj

          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

          • integrating field values in to 1 variable?
            Rob Wunderlich

            Instead of OR use the match() function.

             

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

             

            -Rob

            http://robwunderlich.com

              • Re: integrating field values in to 1 variable?
                Aissam Boumejjane

                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

                  • Re: integrating field values in to 1 variable?
                    Rob Wunderlich

                    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

                      • Re: integrating field values in to 1 variable?
                        Aissam Boumejjane

                        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

                        • Re: integrating field values in to 1 variable?
                          Aissam Boumejjane

                          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.