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!





        • Re: integrating field values in to 1 variable?
          sridhar sridhar



          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 [


              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.



          • 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...)




              • 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




























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




                Thanks a lot for your help!





                  • 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


                    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.



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



                      • 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!!!





                        • 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.