9 Replies Latest reply: Nov 16, 2017 1:25 PM by Marco Wedel RSS

    I need help writing an else statement

    Diwas Karki

      I am new to qlikview. How do I write a script for this:

       

      if(status is complete and "completed on" doesn't exist)
      use string 4
      else
      use "Completed On"

       

      I would really appreciate the help. Thanks

        • Re: I need help writing an else statement
          Ian Wilson

          Hi Diwas

           

          Example below, your statement needs the line:

          IF(Status='Complete' AND ISNULL(Date)=0,String,Date) AS Date2

           

           

          Full example:

           

           

           

           

          Test1:
          Load * inline
          [
          Status,Date,String
          Complete,01/01/17,A
          Complete,02/01/17,B
          Complete, ,C
          Incomplete,01/01/17,D
          Incomplete,02/01/17,E
          Incomplete,,F
          ]
          ;

          Test2:
          Load Status,
          Date,
          String,
          IF(Status='Complete' AND ISNULL(Date)=0,String,Date) AS Date2
          Resident Test1;

          Drop table Test1;

          • Re: I need help writing an else statement
            Marco Wedel

            please post some sample data and your expected result.

             

            thanks

             

            regards

             

            Marco

              • Re: I need help writing an else statement
                Diwas Karki

                Hello Marco,

                Thanks for your reply.

                I have a field name 'ATTRIBUTES' in the database table named spt_audit_event, that holds an xml. It looks like this.

                <Attributes>

                  <Map>

                    <entry key="Business Case" value="test"/>

                    <entry key="Identities" value="TD1J"/>

                    <entry key="Line of Business" value="Auto"/>

                    <entry key="Offices" value="28 - North Atlantic"/>

                    <entry key="Request Type" value="Add"/>

                    <entry key="Requested By" value="Swapnil Agrawal (D03F)"/>

                    <entry key="Requested On" value="Wed, Feb 24 06:42:15 CST 2016"/>

                    <entry key="Template" value="BL - Farm USA - PA2 - DE"/>

                    <entry key="Template Group" value="Business Line Auto"/>

                    <entry key="Work Area" value="Business Lines"/>

                    <entry key="Completed on" value= "Wed, Feb 25 06:42:15 CST 2016"/>

                  </Map>

                </Attributes>

                SPT_AUDIT_EVENT has other field Im using . They are INSTANCE, STATUS and STRING4.

                However, in our database before sometime in 2016, "Attributes" did not store "Completed on" field. It was stored in STRING4. So I need to come up with this condition.

                IF(INSTANE = 'Auto Fire Role Access Request' AND STATUS='Complete' , DO A TEXTBETWEEN IN THE ATTRIBUTES TO BRING IN 'Completed On', if its there

                if 'completed on ' is not there in ATTRIBUTES use string4 as  'Completed On'.

                  • Re: I need help writing an else statement
                    Marco Wedel

                    maybe something like:

                     

                    QlikCommunity_Thread_281313_Pic1.JPG

                     

                    SPT_AUDIT_EVENT:
                    LOAD RecNo() as ID,
                        INSTANCE,
                        STATUS,
                        STRING4,
                        ATTRIBUTES,
                        If(WildMatch(ATTRIBUTES,'*Completed on*'),TextBetween(ATTRIBUTES,'"Completed on" value= "','"/>'),STRING4) as [Completed on]
                    Inline [
                    INSTANCE STATUS STRING4 ATTRIBUTES
                    Instance1 Status1 String4_1 <Attributes><Map><entry key="Business Case" value="test"/><entry key="Identities" value="TD1J"/><entry key="Line of Business" value="Auto"/><entry key="Offices" value="28 - North Atlantic"/><entry key="Request Type" value="Add"/><entry key="Requested By" value="Swapnil Agrawal (D03F)"/><entry key="Requested On" value="Wed, Feb 24 06:42:15 CST 2016"/><entry key="Template" value="BL - Farm USA - PA2 - DE"/><entry key="Template Group" value="Business Line Auto"/><entry key="Work Area" value="Business Lines"/><entry key="Completed on" value= "Wed, Feb 25 06:42:15 CST 2016"/></Map></Attributes>
                    Instance2 Status2 "Tue, Feb 24 05:31:04 CST 2016" <Attributes><Map><entry key="Test attribute" value="some text"/></Map></Attributes>
                    ] (delimiter is '\t');
                    

                     

                    hope this helps

                     

                    regards

                     

                    Marco

                      • Re: I need help writing an else statement
                        Diwas Karki

                        Hello Marco,

                         

                        This is what I ended up using.

                        If(INSTANCE='Auto Fire Role Access Request', STRING3='Complete' AND len(STRING4)<14,TextBetween(ATTRIBUTES, '<entry key="Completed On" value="','"')),
                        If(INSTANCE='Auto Fire Role Access Request', STRING3='Complete' AND len(STRING4)>14, STRING4) As Complete_Date,

                        Script ran successfully. However, string4 is bringing either 0 or -1 .

                          • Re: I need help writing an else statement
                            Marco Wedel

                            Hi,

                             

                            I guess you mean Complete_Date only has 0 and -1 as values.

                            The reason is that you tried to use comma as logical AND operator.

                            So instead of


                            INSTANCE='Auto Fire Role Access Request', STRING3='Complete' AND len(STRING4)>14


                            you could try with


                            INSTANCE='Auto Fire Role Access Request' AND STRING3='Complete' AND len(STRING4)>14



                            Your complete expression however might work as


                            If(INSTANCE='Auto Fire Role Access Request' AND STRING3='Complete',If(Len(STRING4)<14,TextBetween(ATTRIBUTES, '<entry key="Completed On" value="','"'), STRING4)) As Complete_Date



                            hope this helps


                            regards


                            Marco

                    • Re: I need help writing an else statement
                      Diwas Karki

                      If(INSTANCE='Auto Fire Role Access Request' AND STRING3='Complete' AND len(STRING4)<18,TextBetween(ATTRIBUTES, '<entry key="Completed On" value="', '"'),
                      If(INSTANCE='Auto Fire Role Access Request'AND STRING3='Complete' AND len(STRING4)>18, STRING4)) As Completed_Date,

                       

                      Looks like it was bringing 0 and -1 and because the length was at least 17 at all times.