8 Replies Latest reply: Aug 10, 2017 2:50 PM by Ruben Marin RSS

    IF then and statement help

    Joshua Russin

      this is my set expression for my dimension in a table.

       

      =If (Assigned_To_QM = 'Alpha', Assigned_To_QM,

              If (Assigned_To_QM = 'Bravo', Assigned_To_QM,

              If (Assigned_To_QM = 'Charlie', Assigned_To_QM,

              If (Assigned_To_QM = 'Delta', Assigned_To_QM))))

            

      I am trying to make an Alpha_1 and an Alpha_2.

      I want Alpha_1 to be when Area_QM -= 'yellow' or 'green'

      and Alpha_2 to be when Area_QM = 'yellow' or 'green'

       

      If (Assigned_To_QM = 'Alpha', 'Alpha_1',

           If (Assigned_To_QM = 'Alpha', 'Alpha_2'))


      Any help?

        • Re: IF then and statement help
          Ruben Marin

          Hi Joshua, maybe?:

           

          =If (Assigned_To_QM = 'Alpha',

                 If(Match(Area_QM, 'yellow', 'green'), 'Alpha_2', 'Alpha_1'),

                  If (Assigned_To_QM = 'Bravo', Assigned_To_QM,

                  If (Assigned_To_QM = 'Charlie', Assigned_To_QM,

                  If (Assigned_To_QM = 'Delta', Assigned_To_QM))))

            • Re: IF then and statement help
              Joshua Russin

              This is the result I get.

              help.jpg

                • Re: IF then and statement help
                  Ruben Marin

                  What was the result before? There are recors with Assigned_To_QM ='Alpha' and with Area_QM='yellow' or 'green' (all lowercase)?


                  Also, what's the relathionship between Assigned_To_QM and Area_QM? It's 1 to 1?

                    • Re: IF then and statement help
                      Joshua Russin

                      Here is my app example

                      • Re: IF then and statement help
                        Joshua Russin

                        If (Assigned_To_QM = 'Candace Bennett',

                                If(Match(Client_QM, 'HT Medical', 'SeaSpine', 'SMV Scientific'), Assigned_To_QM, 'Candy MI'),

                                If (Assigned_To_QM = 'Candace Bennett',

                                If(Match(Client_QM, 'ConforMIS, Inc.', 'Integra', 'Mako Surgical'), Assigned_To_QM, 'Candy Non-MI'),

                                If (Assigned_To_QM = 'Anwar Powell', 'Bravo',

                                If (Assigned_To_QM = 'Cody Grisanti', 'Charlie',

                                If (Assigned_To_QM = 'Christine Lane', 'Delta')))))

                         

                        I've tried this as well and this doesn't work either.

                          • Re: IF then and statement help
                            Ruben Marin

                            Not sure of what you want, but maybe:

                            If (Assigned_To_QM = 'Candace Bennett'

                              , If(Match(Client_QM, 'HT Medical', 'SeaSpine', 'SMV Scientific')

                                , 'Candy MI'

                                , If(Match(Client_QM, 'ConforMIS, Inc.', 'Integra', 'Mako Surgical')

                                  , 'Candy Non-MI'

                                  , Assigned_To_QM))

                            If (Assigned_To_QM = 'Anwar Powell', 'Bravo',

                            If (Assigned_To_QM = 'Cody Grisanti', 'Charlie',

                            If (Assigned_To_QM = 'Christine Lane', 'Delta'))))

                             

                            If doesn't works you need to provide pore info on what you expect and why is not working.

                              • Re: IF then and statement help
                                Joshua Russin

                                I am trying to show the number of Tracking_QM (in the measure, it is a count of Tracking_QM)

                                The first column is Project Manager, but the list provided in the database exceeds the info I need. I just need the results for only select people. And some people have different clients. Ex. 'Candy MI' is HT Medical, SeaSpine and SMV Scientific. 'Candy Non-MI' is ConforMIS, Inc., Integra, and Mako Surgical.

                                This is a screenshot from my excel table derived from an excel doc. Just trying to remake here in Qlik.

                                help.jpg

                                  • Re: IF then and statement help
                                    Ruben Marin

                                    Hi, I don't know why but Trim() isn't working to remove the last blank space.

                                     

                                    I have exported the Client_QM from the app to excel and the copied the text just at it was exported to excel, this way seems work, I have only did for SMV Scientific and SeaSpine.

                                     

                                    There was also a need of an extra comma:

                                    =If(Assigned_To_QM = 'Candace Bennett'

                                      , If(Match(Trim(Client_QM), 'HT Medical', 'SeaSpine ', 'SMV Scientific ')

                                        , 'Candy MI'

                                        , If(Match(Client_QM, 'ConforMIS, Inc.', 'Integra', 'Mako Surgical')

                                          , 'Candy Non-MI'

                                          , Client_QM)), // Maybe this line should be : ", Null()),"; or ", Assigned_To_QM)),"

                                    If (Assigned_To_QM = 'Anwar Powell', 'Bravo',

                                    If (Assigned_To_QM = 'Cody Grisanti', 'Charlie',

                                    If (Assigned_To_QM = 'Christine Lane', 'Delta'))))