14 Replies Latest reply on Sep 10, 2018 9:18 PM by lei.chen.0

    Tableau Lookup based on if else conditions

    Chandu p

      Hello, Can some one help me with this, I need to write a calculative field

      "when action1 status is Delete DOS followed by any status flag that should be match with action 2 and action 3 .If not then show it as

      error. This is only when action starts with Delete DOS status flag. These flags are pulling from separate column.

      Remaining all status flags need to compare with three actions in this same calculative field and based on following condition

      action1=action2 then 0, action2 = action3 then 0 and action1=action3 then 0 other wise 1 error related to code.

       

      This is a valid case and error is zero.

      img4.JPG.

      In Below Image code I743 has Delete DOS followed by another status flag which is matching with all actions but not matching with first status, so error is 1

      I 771-  error is 0

      img1.JPG

        • 1. Re: Tableau Lookup based on if else conditions
          lei.chen.0

          Hello Chandu,

           

          Could you attach a sample workbook?

           

           

          Regards

          Lei

          1 of 1 people found this helpful
          • 2. Re: Tableau Lookup based on if else conditions
            Chandu p

            Hello Lei,

             

            Attached sample workbook.

            • 3. Re: Tableau Lookup based on if else conditions
              lei.chen.0

              Hello Chandu,

               

              From your description, I assume that "code error" is for each User-ID-Code.

              So I moved the calculated field next to "Code".

               

              Please refer to the attached workbook for details.

               

              Regards

              Lei

              1 of 1 people found this helpful
              • 4. Re: Tableau Lookup based on if else conditions
                Chandu p

                Hey Lei,

                 

                Thanks for your work but some codes giving wrong calculation.

                 

                K8907,M9897,N183,I495,J449,

                 

                J449- Action1 is not equal to action3 so it is an error. When all actions are there then need to compare with

                action1 and action3 and  ignore action2

                im1.JPG

                 

                K8907- Action3 null then compare 1 and 2 so here 1 error

                M9897- Action1 is Null but action2 has value so this is not valid.

                when Action 3 is null and if action 2 has value and action 1 has value then compare these two

                if action3,action1 are Null and action 2 has value then 1 error.

                 

                im2.JPG

                 

                N183- 1 and 2 are not same and 3 is null so need to compare 1 and 2 , so error is 1

                pg3.JPG

                • 5. Re: Tableau Lookup based on if else conditions
                  lei.chen.0

                  Hello Chandu,

                   

                  I got a little confused about the calculation logic.

                  Please provide a complete and accurate description of your requirements.

                   

                  According to,

                  Chandu p wrote:

                   

                  a calculative field "when action1 status is Delete DOS followed by any status flag that should be match with action 2 and action 3 .

                  So all the codes without "Delete DOS" in action 1 are assumed as no error (error code = 0)

                   

                  Also, please confirm if this assumption is correct for your requirement.

                  Lei Chen wrote:

                   

                  From your description, I assume that "code error" is for each User-ID-Code.

                  It seems you need an error code for each data row.

                   

                   

                  Regards

                  Lei

                  1 of 1 people found this helpful
                  • 6. Re: Tableau Lookup based on if else conditions
                    Chandu p

                    Hey Lei,

                    Each ID has multiple codes and any code with value of Delete DOS and followed by any value that should match with action 2 and action 3, If not then show it as error. If Null in action 1 or 3 or 2 and remaining actions are same then also it’s a error. This is only when action value is with Delete DOS in any code.

                     

                    Code              Action1               Action2                Action3                 Error

                    F-315           Delete DOS       Delete DOS            Delete DOS

                                          Invalid add        Invalid add             Invalid add                 0

                    F-890           Delete DOS       Delete DOS            Null

                                          Invalid add        Invalid add              Null                           1

                    F-879                Null                Delete DOS            Delete DOS           

                                               Null               New add                 New add                   1

                     

                    When Action1 is Null and action2 has value and action3 null then error

                      I-980                 Null                   confirm                       Null                   1

                    when 1 and 2 same 3 is null then 0 error

                      k-478             new add                 new add                   Null                   0

                    All actions have value and 1,3 both are same and 2 is different  then 0 error. Ignore action2

                    when all actions are showing value and compare only between 1 and 3

                      J-890              confirm                     delete                 confirm               0

                    Action 1 null 2 and 3 are same or not same then also error

                    M-900                  Null                        Confirm            Confirm               1

                    N-909                   Null                         confirm             delete                1

                    • 7. Re: Tableau Lookup based on if else conditions
                      lei.chen.0

                      Hello Chandu,

                       

                      Thank you for the clarification.

                       

                      A modified workbook is attached.

                       

                       

                      Regards

                      Lei

                      1 of 1 people found this helpful
                      • 8. Re: Tableau Lookup based on if else conditions
                        Chandu p

                        Hey Lei,

                        Appreciate your work.Thank you for your quick response. only one thing is not showing correct value here.

                        when action1,2 is Null and action3 has value that should be 1 but it's showing Null instead of  1

                        and when

                        Delete DOS      Delete DOS            Null

                        Invalid add        Invalid add          Null      is there then it's '0' sorry I put it in a wrong way earlier.   

                        Calculated one more filed with ID error tried from my end but throwing error.

                        Can you help me with this field.Attached work book.

                         

                                 

                        • 9. Re: Tableau Lookup based on if else conditions
                          Chandu p

                          Hey Lei,

                          Appreciate your work.Thank you for your quick response. only one thing is not showing correct value here.

                          when action1,2 is Null and action3 has value that should be 1error  but it's showing Null instead of  1

                          and when

                          Delete DOS      Delete DOS            Null

                          Invalid add        Invalid add          Null      is there then it's 0 error. sorry I put it in a wrong way earlier. 

                          Calculated one more filed with ID error tried from my end but throwing error.

                          Can you help me with this field.Attached work book.

                          • 10. Re: Tableau Lookup based on if else conditions
                            lei.chen.0

                            Hello Chandu,

                             

                            Chandu p wrote:

                             

                            when action1,2 is Null and action3 has value that should be 1error  but it's showing Null instead of  1

                             

                            This new requirement is simple to deal with.

                            Just add condition in the calculated field [Code error new].

                             

                            { FIXED [User], [ID], [Code]:

                            IF MAX(IIF([Action1]='Delete DOS',1,0)) = 1 THEN

                                MAX(IIF(NOT((IFNULL([Action1],'') = IFNULL([Action 2],'') AND

                                IFNULL([Action 2],'') = IFNULL([Action 3], ''))), 1, 0))

                            ELSE

                                MAX(CASE [FLAG]

                                WHEN '101' THEN 1

                                WHEN '100' THEN 1

                                WHEN '110' THEN 1

                                WHEN '001' THEN IIF([Action1]=[Action 2],0,1)

                                WHEN '010' THEN IIF([Action1]=[Action 3],0,1)

                                WHEN '000' THEN IIF([Action1]=[Action 3],0,1)

                                END)

                            END}

                             

                            Chandu p wrote:

                             

                            Delete DOS      Delete DOS            Null

                            Invalid add        Invalid add          Null      is there then it's 0 error. sorry I put it in a wrong way earlier.

                            Calculated one more filed with ID error tried from my end but throwing error.

                             

                             

                            However, this logic is not clear.

                             

                            You might have noticed that [FLAG] is the NULL flag for Action1-Action2-Action3 .

                             

                            In the current calculation, the first logic condition break is if the code has any "Delete DOS",

                            if yes, it obeys the "Delete DOS" logic, if no, it goes to the second break (CASE).

                             

                            The second break looks at each row of the code, and only when all the rows of one code has no error (0), the code has no error (0).

                            Row level error logic is based on [FLAG].

                            [FLAG] contains of 3 letters, each of them '0' or '1', thus there are 8 possible combinations.

                            Some of the combination might never happen in real data, for example '111'.

                             

                            Then return to the reason why I think your second logic is not clear.

                             

                            Your example,

                             

                            Delete DOS      Delete DOS        Null

                            Invalid add        Invalid add          Null

                             

                            Should it be considered as a sub-case under [FLAG] '110' , or a sub-case under "Delete DOS" ?

                             

                            Another example, how about this case?

                             

                            Delete DOS      Delete DOS        Delete DOS

                            Invalid add        Invalid add          Null

                             

                             

                            In conclusion, again, please provide a complete and accurate description of your requirements.

                            It'd better to make clear about the logic before actually writing the calculation, otherwise there would be an endless "this calculation doesn't work for this specific case".

                             

                             

                            Regards

                            Lei

                            1 of 1 people found this helpful
                            • 11. Re: Tableau Lookup based on if else conditions
                              Chandu p

                              Hello Lei,

                              Thank You so much for your Patience. Here is the explanation

                              ex1.JPG

                              Your understanding is correct- if the code has any "Delete DOS", if yes, it obeys the "Delete DOS" logic, if no, it goes to the second break -

                              and in above case error count is 0 because Action 3 is Null and action1,2 are same.

                               

                              Another example, how about this case?

                               

                              Delete DOS      Delete DOS        Delete DOS

                              Invalid add        Invalid add          Null

                               

                              Above scenario is not valid. If it's there then error count is 1

                               

                              code      Action1                 Action2                Action3            Error

                                xx        Null                  Confirm             Null                0

                                xx        Null                  Null                 Delete              0

                              xxx        Delete DOS     Delete DOS     Null       

                                              Invalid Add       Invalid Add      Null                    0

                              Above Scenarios need to be update in the existing calculation .Everything is working good

                              • 12. Re: Tableau Lookup based on if else conditions
                                lei.chen.0

                                Hello Chandu,

                                 

                                Workbook updated.

                                 

                                I added several rows since the test data doesn't contain the case you mentioned.

                                 

                                 

                                 

                                Regards

                                Lei

                                • 13. Re: Tableau Lookup based on if else conditions
                                  Chandu p

                                  Hello Lei,

                                   

                                  I am not sure why the calculation is not working with the data attached. Required result is in the "column codes with errors" of the attached workbook.

                                   

                                   

                                   

                                  test2 code error is 0 not 1.

                                  One more scenario is

                                  Null  ----  confirm ---- Null is also a 0 error not 1, Attached work book.

                                  • 14. Re: Tableau Lookup based on if else conditions
                                    lei.chen.0

                                    Hello Chandu,

                                     

                                    As I've repeated again and again, I'm fairily unconfortable about the endless "one more secnario".

                                     

                                    And the point here is that, I'm willing to help you to know how to write the calculation, not just do it for you and you never know how or even have a simple try by yourself.

                                     

                                    I've explained how FLAG logic works, so this is very easy to achieve.

                                    It's just a little change from 1 to 0.

                                     

                                    Null  ----  confirm ---- Null is also a 0 error not 1, Attached work book.

                                     

                                     

                                    Regards

                                    Lei