23 Replies Latest reply: Mar 3, 2015 6:18 AM by Timanshu Virmani RSS

    Multiple nested if statements

    Andre Nel

      Hi all

       

      Can someone please help me with the following load statement:

       

      Groupings2:

      Load *,

      If(0 <= seats_reamaining <= 10 , 'A',

      If(10 < seats_reamaining <= 20 , 'B',

      If(20 < seats_reamaining <= 30 , 'C',

      If(30 < seats_reamaining <= 40 , 'D',

      If(40 < seats_reamaining <= 50 , 'E',

      If(50 < seats_reamaining <= 60 , 'F',

      If(60 < seats_reamaining <= 70 , 'G',

      If(70 < seats_reamaining <= 80 , 'H',

      If(80 < seats_reamaining <= 90 , 'I',

      If(90 < seats_reamaining <= 100 , 'J',

      If(100 < seats_reamaining <= 110 , 'K',

      If(110 < seats_reamaining <= 120 , 'L',

      If(120 < seats_reamaining <= 130 , 'M',

      If(130 < seats_reamaining <= 140 , 'N',

      If(140 < seats_reamaining <= 150 , 'O',

      If(150 < seats_reamaining <= 160 , 'P',

      If(160 < seats_reamaining <= 170 , 'Q',

      If(170 < seats_reamaining <= 180 , 'R', 'Cannot Calculate')))))))))))))))))) as Seat_Class

      Resident Groupings1;

       

      I keep getting the following error:

      Error in expression:

      ')' expected

       

      I don't understand this as the correct number of parenthesis is included to close all the if statements. Any help will be greatly appreciated.

      ps I'm fairly new to QV.

        • Re: Multiple nested if statements
          sam wisegamzee

          Hi Andre,

          try like this

           

          load *,

          if(seats_remaining>0 and seats_remaining <=10,'A',

               if(seats_remaining>10 and seats_remaining <=20,'B','Cannot Calculate')) as Seat_Class

          resident Groups1;

           

          Regards

          samwisegamzee


          • Re: Multiple nested if statements
            jagan mohan rao appala

            Hi,

             

            Use this expression

             

            Groupings2:

            Load *,

            If(seats_reamaining <= 10 , 'A',

            If(seats_reamaining <= 20 , 'B',

            If(seats_reamaining <= 30 , 'C',

            If(seats_reamaining <= 40 , 'D',

            If(seats_reamaining <= 50 , 'E',

            If(seats_reamaining <= 60 , 'F',

            If(seats_reamaining <= 70 , 'G',

            If(seats_reamaining <= 80 , 'H',

            If(seats_reamaining <= 90 , 'I',

            If(seats_reamaining <= 100 , 'J',

            If(seats_reamaining <= 110 , 'K',

            If(seats_reamaining <= 120 , 'L',

            If(seats_reamaining <= 130 , 'M',

            If(seats_reamaining <= 140 , 'N',

            If(seats_reamaining <= 150 , 'O',

            If(seats_reamaining <= 160 , 'P',

            If(seats_reamaining <= 170 , 'Q',

            If(seats_reamaining <= 180 , 'R', 'Cannot Calculate')))))))))))))))))) as Seat_Class

            Resident Groupings1;

             

            Regards,

            Jagan.

            • Re: Multiple nested if statements
              Timanshu Virmani

              Hi Andre,

              Qlikview does not understand the comparison between values as described in above load statement provided by you.

               

              To make that logic understood by Qlikview,  Just Paste this instead of provided by you :

               

              Groupings2:

              Load *,

              If(0 <= seats_reamaining and seats_reamaining <= 10 , 'A',

              If(10 < seats_reamaining and seats_reamaining <= 20 , 'B',

              If(20 < seats_reamaining and seats_reamaining <= 30 , 'C',

              If(30 < seats_reamaining and seats_reamaining <= 40 , 'D',

              If(40 < seats_reamaining and seats_reamaining <= 50 , 'E',

              If(50 < seats_reamaining and seats_reamaining <= 60 , 'F',

              If(60 < seats_reamaining and seats_reamaining <= 70 , 'G',

              If(70 < seats_reamaining and seats_reamaining <= 80 , 'H',

              If(80 < seats_reamaining and seats_reamaining <= 90 , 'I',

              If(90 < seats_reamaining and seats_reamaining <= 100 , 'J',

              If(100 < seats_reamaining and seats_reamaining <= 110 , 'K',

              If(110 < seats_reamaining and seats_reamaining <= 120 , 'L',

              If(120 < seats_reamaining and seats_reamaining <= 130 , 'M',

              If(130 < seats_reamaining and seats_reamaining <= 140 , 'N',

              If(140 < seats_reamaining and seats_reamaining <= 150 , 'O',

              If(150 < seats_reamaining and seats_reamaining <= 160 , 'P',

              If(160 < seats_reamaining and seats_reamaining <= 170 , 'Q',

              If(170 < seats_reamaining and seats_reamaining <= 180 , 'R', 'Cannot Calculate')))))))))))))))))) as Seat_Class

              Resident Groupings1;

                • Re: Multiple nested if statements
                  Andre Nel

                  Hi Timanshu

                   

                  Thanks for this, it worked perfectly. I come from a SAS background so this is how I have always understood dual constraints.

                    • Re: Multiple nested if statements
                      Timanshu Virmani

                      Hi Andre,

                       

                      Your Welcome. You can mark the answer as correct if it solved your purpose. Not only this question,but for every question you put up. This will help other peoples to find the answers as if they can have the same query.

                      • Re: Multiple nested if statements
                        jagan mohan rao appala

                        Hi Andre,

                         

                        Did you tried this simple expression?  No need of AND and many conditions

                         

                        Groupings2:

                        Load *,

                        If(seats_reamaining <= 10 , 'A',

                        If(seats_reamaining <= 20 , 'B',

                        If(seats_reamaining <= 30 , 'C',

                        If(seats_reamaining <= 40 , 'D',

                        If(seats_reamaining <= 50 , 'E',

                        If(seats_reamaining <= 60 , 'F',

                        If(seats_reamaining <= 70 , 'G',

                        If(seats_reamaining <= 80 , 'H',

                        If(seats_reamaining <= 90 , 'I',

                        If(seats_reamaining <= 100 , 'J',

                        If(seats_reamaining <= 110 , 'K',

                        If(seats_reamaining <= 120 , 'L',

                        If(seats_reamaining <= 130 , 'M',

                        If(seats_reamaining <= 140 , 'N',

                        If(seats_reamaining <= 150 , 'O',

                        If(seats_reamaining <= 160 , 'P',

                        If(seats_reamaining <= 170 , 'Q',

                        If(seats_reamaining <= 180 , 'R', 'Cannot Calculate')))))))))))))))))) as Seat_Class

                        Resident Groupings1;

                         

                        Regards,

                        Jagan.

                    • Re: Multiple nested if statements
                      Marcus Sommer

                      I suggest to replace the nested if-loops completely through a mapping-approach. Either directly with a mapping-table or with a combined pick(match()) function:

                       

                      http://community.qlik.com/message/354694#354694

                      http://community.qlik.com/message/593232#593232

                       

                      - Marcus

                      • Re: Multiple nested if statements
                        Rupert Cavendish

                        A bit simpler:

                         

                        if(seats/10 > 18,
                        'Cannot Calculate',
                        chr(seats/10+65)
                        ) as Seat_Class

                         

                        Hope this helps

                        • Re: Multiple nested if statements

                          I have a similar problem

                          • Re: Multiple nested if statements
                            Bernd Jägle

                            I always prefer the mapping appoach. To define my mappings I use an external table, like xls or txt. Simmple to maintain and add values, when needed.