14 Replies Latest reply: Oct 19, 2016 6:47 AM by Supriya R RSS

    Trying to create expression for If..then..elseif..else..end if

      I'm new to Qlik. I am trying to create this chart expression, and I am having difficulty.

      I am trying to apply formulas to the total if these conditions are met. I am pretty inexperienced with syntax for sql, qlik.

      Can anyone lend a hand?

      Thanks- DM

       

      if {SeriesCode} = "S" and {ShipToAddrState} = "CA" and ({ShipTOCommPlanID} <> "1726" and {ShipTOCommPlanID} <> "1727")

      then {ExtAmt} * (.94)

      else if {SeriesCode} = "I" then {ExtAmt}* (.42)

      else if { MajLink} = "YK" then {ExtAmt} * (.39)

      else if {SeriesCode} = "Y" then {ExtAmt} * (.42)

      else if {SeriesCode} = "Z" then {ExtAmt} * (0)

      else {ExtAmt}

        • Re: Trying to create expression for If..then..elseif..else..end if
          Rebecca Molstad

          Try something like this:

           

          if

          (SeriesCode='S' and ShipToAddrState='CA' and ShipTOCommPlanID<>'1726' and ShipTOCommPlanID<>'1727',

          ExtAmt*.94,

          if(SeriesCode='I',ExtAmt*.42,

          if(MajLink='YK',ExtAmt*.39,

          if(SeriesCode='Y',ExtAmt*0,

          if(SeriesCode='Z',ExtAmt*0,ExtAmt)))))

          • Re: Trying to create expression for If..then..elseif..else..end if

            The expression Rebecca posted will work nicely.

             

            With such a complex (or just long) expression, you may want to consider some options to simplify things for your chart expression.  You could perhaps put some of this logic in the load script.  Or for this expression, since the multiplication factor seems tied to the [SeriesCode], you could load a table in the model that includes an assoication (i.e., a Series table), or you could combine these approaches using ApplyMap.

              • Re: Trying to create expression for If..then..elseif..else..end if

                Thanks Donald, Rebecca's expression does work nicely. 

                I am working with it to try how and where to apply it correctly.

                I would love to put this logic into the load script or,

                load a table in the model that includes an assiciation (i.e., a Series table),

                or combine the approaches using an ApplyMap.

                 

                I will continue to research these 3 options,  Could you further assist?

                 

                Drew

                  • Re: Trying to create expression for If..then..elseif..else..end if

                    Drew, I'm happy to try to help.  Any way you could post your app or data model so that I can see what you're working with?  I can guess at what you have and offer some ideas, but obviously it would be easier (and more meaningful) to work with your data.

                      • Re: Trying to create expression for If..then..elseif..else..end if

                        Many Thanks. Will Post on Monday morning !

                          • Re: Trying to create expression for If..then..elseif..else..end if

                            Donald-

                             

                            Here is a copy of script.

                            Im grateful for any ideas to incorporate the expressions into my script.

                             

                            Drew

                              • Re: Trying to create expression for If..then..elseif..else..end if

                                While it is always a judgment call as to whether you include logic like this as expressions in your charts or in the load script, sometimes you may find it useful to put some of the complexity in the load script, thus simplifying your charts.  In any case, just some food for thought.  In your expression, the logic is a bit complex, but even so it may be a bit simplified if you setup a mapping table to handle Series Code I, Y, and Z, then you could add an expression to your load table like this:

                                 

                                SeriesMap:
                                Mapping Load * Inline [
                                Code, Factor
                                I, 0.42
                                Y, 0.42
                                Z, 0
                                ];
                                
                                
                                Fact:
                                LOAD 
                                          *,
                                          IF(SeriesCode='S' And ShipToCommPlanID <> '1726' And ShipToCommPlanID <> '1727' And ShipToAddrState = 'CA', ExtAmt * 0.94,
                                                    IF( MajLink = 'YK', ExtAmt * 0.39, 
                                                              ExtAmt * ApplyMap( 'SeriesMap', SeriesCode, 1 )
                                                    )
                                          ) As CustomCalc;
                                LOAD 
                                           SeriesCode, 
                                     ShipToAddrState, 
                                     ShipToCommPlanID, 
                                     MajLink, 
                                     etc....
                                FROM
                                (your data source);
                                
                                

                                 

                                Here I've just used a preceding load for illustration, but you could add such an expression in your main LOAD as needed.

                                 

                                I've modified the inline IF so that we can leverage a mapping lookup table, and in the event the Series Code is not found in the map, then the factor used will simply be 1.

                                 

                                Hope that helps to give you some ideas, in any case.

                                  • Re: Trying to create expression for If..then..elseif..else..end if

                                    Thanks Donald.

                                     

                                    Very Helpful and Informative.

                                     

                                    Small Obstacle:      I am geting this script error mesage:

                                     

                                    Field not found - <SeriesCode>

                                    SQL SELECT ExtAmt,

                                        ItemUserFld3,

                                        ShipToAddrState,

                                        ShipTOCommPlanID

                                    FROM "whs_mas500_app".dbo.vdvInvoiceLineSadlier

                                     

                                    Do you think it could have something to do with the fact that I am trying to pull from 2 diffrnt locations?

                                     

                                    SeriesCode

                                    FROM MAS500Custom.dbo."WHS_SeriesTen_2012";

                                     

                                    AND

                                     

                                    ExtAmt,

                                    ItemUserFld3,

                                    ShipToAddrState,

                                    ShipTOCommPlanID

                                    FROM MAS500Custom.dbo."WHS_SeriesTen_2012";

                                    Thanks,

                                    Drew

                                     

                                      • Re: Trying to create expression for If..then..elseif..else..end if

                                        Drew, you have a few issues here that are going to prevent a successful load.  You are getting the error because of the reference to SeriesCode under "Fact:" but that field is not avaialble in the LOAD/SELECT you do just under.  But beyond that, there are other problems here.

                                         

                                        To help you, I need to understand how these two tables (or views) that you are loading relate to each other.  In your custom calculation, you are referencing fields from both of the loads here:

                                         

                                        "whs_mas500_app".dbo.vdvInvoiceLineSadlier (contains ShipToAddrState, ShipToCommPlanID, ExtAmt)

                                         

                                        MAS500Custom.dbo."WHS_SeriesTen_2012" (contains SeriesCode, MajLink)

                                         

                                        So how do you relate those two entities?

                                      • Re: Trying to create expression for If..then..elseif..else..end if
                                        Supriya R

                                        Hey Donald,

                                         

                                        i have a inline statement where i need to load expresion value

                                         

                                        MetricsDims:

                                        LOAD Metrics , Replace(Datavalue, '@','$') INLINE [

                                              Metrics  , Datavalue

                                            Invitations , @(vProg_Last_Campaign_Completion_Count)

                                            Delivered ,  @(vParlege_Last_Campaign_Completion_Count)

                                        ];

                                        Currently i am getting like this

                                        Invitations , @(vProg_Last_Campaign_Completion_Count)

                                            Delivered , @(vParlege_Last_Campaign_Completion_Count)

                                         

                                        but i need actual values for variables

                                        so that in table i get like this:

                                            Invitations , 3

                                            Delivered ,  4

                                         

                                        I know using above code in script to inline statement doesnot work,i have to use pick and match by refering this Expressions in Fields

                                         

                                        But there i didnt get what is column1: Name. please help me on this