1 2 Previous Next 16 Replies Latest reply on Mar 16, 2016 1:13 AM by Mark Fraser

    Renaming using calcs

    Hannah Williams

      Good Morning,

       

      I have a column in my table that records error messages. The examples below are all the same message, but don't group as include some unique data.

       

      "Client Error! referer: 2RHFCKEZY8SQAWTBR, error: Script error.  0"

      "Client Error! referer: ZM1BJ2BK64BOUCVTM, error: Script error.  0"

      "Client Error! referer: 2RHFCKEZY8SQAWTBR, error: Script error.  0"

      "Client Error! referer: ZM1BJ2BK64BOUCVTM, error: Script error.  0"

      "Client Error! referer: ZEOXKKSLQ3YJY4FZF, error: Script error.  0"

       

      "L1ZJ2H4DL: java.lang.IllegalArgumentException: obj parameter must not be null, returning from database instead"

      "L112344DL: java.lang.IllegalArgumentException: obj parameter must not be null, returning from database instead"

      "MADF124DL: java.lang.IllegalArgumentException: obj parameter must not be null, returning from database instead"

      "L1ZR11456: java.lang.IllegalArgumentException: obj parameter must not be null, returning from database instead"

       

      The above is just an example, there are different cases, and because new session data is created, I can't use the group function right away, as the new data doesn't get included.

       

      Is there a way using calcs to rename the set, based on what the string is like? for example:  when message like '%Client Error! referer%' and message like '%error: Script error.  0%' then = Script Error

       

       

      Thanks

       

      Hannah

        • 1. Re: Renaming using calcs
          Tharashasank Davuluru

          Hi

           

          yes we  can do that by using string functions in calculated field:

           

          Contains(string,substring)

          endswith(string,substring)

          • 2. Re: Renaming using calcs
            Hannah Williams

            would you mind providing an example? I'm very new to Tableau calcs.

             

            IF  [Message]  CONTAINS (Client Error! referer)

            AND [Message] CONTAINS (error: Script error.  0)

            THEN = Script Error

            END

            • 3. Re: Renaming using calcs
              Tharashasank Davuluru

              HI,

               

               

              1.Case [Message]

              When CONTAINS "Client Error! referer" then "Client Error! referer"

              When CONTAINS "error: Script error.  0" then "Script error"

              end

               

               

              2. If Contains([Message],"Client Error! referer")  then "Client Error! referer"

              ElseIf Contains([Message],"error: Script error.  0")  then "Script error"

              else ""  end

               

               

              Please look at this post for string calculations:String Calculations in Tableau - Clearly and Simply

              • 4. Re: Renaming using calcs
                Hannah Williams

                I'm still getting an error. To be clear I'm trying to use 2 contains clauses to find the right param, so as to filters out similar messages. I only want to rename to "Script error".

                 

                CASE [Error Message]

                WHEN CONTAINS "Client Error! referer" 

                AND WHEN CONTAINS "error: Script error.  0"

                THEN "Script error"

                END

                 

                Thanks, your feedback is much appreciated.

                • 5. Re: Renaming using calcs
                  Tharashasank Davuluru

                  Hi,

                   

                  CASE [Error Message]

                  WHEN CONTAINS "Client Error! referer"

                  AND WHEN CONTAINS "error: Script error.  0"

                  THEN "Script error"

                  END

                   

                  remove the and which i have strike out.

                  • 6. Re: Renaming using calcs
                    Hannah Williams

                    That makes sense:) but still seeing an error: Expected 'Then' to match 'Case' at character 0

                    • 7. Re: Renaming using calcs
                      Mark Fraser

                      Hi Hannah

                       

                      There are errors in what you wrote...  you are missing THEN for example.

                      Here is a way,,, I have broken it in to steps, to hopefully help explain -

                       

                      Source Data

                      Test for the error -

                      Error1 = CONTAINS([Error],"Client Error!")

                      and the other

                      Erro2 = CONTAINS([Error],"error: Script error")

                       

                      Then combine the tests...

                      IF [error1] = TRUE and [error2] = True

                      THEN 'Client Error'

                      ELSE NULL

                      END

                       

                      Output (v9.2.4 attached)

                      You can amend the error tests, and swap the AND to OR in the combination - it depends what you need/ want to do.

                       

                      The main thing -

                      With AND, both tests have to be true, otherwise it will fail

                      with OR, if either of the tests are true, the test will work

                       

                      Notice on mine, with the AND, only the rows with both messages were flagged.

                       

                      I hope that make sense!

                       

                      Cheers

                      Mark

                      • 8. Re: Renaming using calcs
                        Mark Fraser

                        Tharashasank Davuluru wrote:

                         

                        Hi,

                         

                        CASE [Error Message]

                        WHEN CONTAINS "Client Error! referer"

                        AND WHEN CONTAINS "error: Script error.  0"

                        THEN "Script error"

                        END

                         

                        remove the and which i have strike out.

                        Errr.... that's not quite right...

                         

                        This is what you wanted...

                        • 9. Re: Renaming using calcs
                          Hannah Williams

                          Thanks Mark, yes that makes sense, but I think it would be easier for me to just group/rename to start with using the sql query. I have a good few of these to handle.  

                          1 of 1 people found this helpful
                          • 10. Re: Renaming using calcs
                            Mark Fraser

                            Hi Hannah

                             

                            If you have a lot, that maybe your best bet.

                            FYI - depending on volume, you could use wildcards to reduce the amount of tests you write (your looking for commonality between the errors)

                            For example Error LIKE '%error%' would return any string with 'error' anywhere in the string, it should have picked up both the errors described above, in a single test...

                             

                            With regard to your question - is it now answered?

                            Assuming so, please could you mark the answer which helped you most - it keeps the forum tidy, and potentially helps others in the future

                             

                            If there us anything else, please let us know

                             

                            Cheers

                            Mark

                            1 of 1 people found this helpful
                            • 11. Re: Renaming using calcs
                              Mark Fraser

                              With regard to your question - is it now answered?

                              Assuming so, please could you mark the answer which helped you most - it keeps the forum tidy, and potentially helps others in the future

                              Sorry, just seen you have already!

                              Many thanks

                              • 12. Re: Renaming using calcs
                                Hannah Williams

                                Yes, the only way I think it could work for me better using tableau is if the one calc could hold multiple updates, so all new messages go into the new column, and if else display what was in the original column.,

                                 

                                i.e.

                                 

                                IF  CONTAINS([Error Message], "Client Error! referer")

                                AND CONTAINS([Error Message], "Client Error! referer")

                                THEN "Script error"

                                 

                                IF  CONTAINS([Error Message], "Tab not found in CMS")

                                THEN "Tab not found in CMS"

                                 

                                IF  CONTAINS([Error Message], "Baggage All")

                                THEN "Baggage All"

                                 

                                ELSE = [Error Message]

                                END

                                 

                                 

                                 

                                But not sure if thats possible....

                                • 13. Re: Renaming using calcs
                                  Mark Fraser

                                  Hi Hannah

                                   

                                  That is possible.

                                  You would use the ELSEIF instead of ELSE

                                  Each ELSEIF is your next test...

                                   

                                  IF [field] = '1' THEN 1

                                  ELSEIF [field] = '2' THEN 2

                                  ELSEIF and so on...

                                   

                                  If you have any questions, let me know

                                   

                                  So... (it may not be perfect, but I hope you see the technique...)

                                   

                                  IF  CONTAINS([Error Message], "Client Error! referer")

                                  AND CONTAINS([Error Message], "Client Error! referer")

                                  THEN "Script error"

                                   

                                  ELSEIF  CONTAINS([Error Message], "Tab not found in CMS")

                                  THEN "Tab not found in CMS"

                                   

                                  ELSEIF  CONTAINS([Error Message], "Baggage All")

                                  THEN "Baggage All"

                                   

                                  ELSE = [Error Message]

                                  END

                                   

                                  1 of 1 people found this helpful
                                  • 14. Re: Renaming using calcs
                                    Hannah Williams

                                    This is working really well for me apart from the end line, which causes the calc to fail. Any idea why?

                                     

                                    ELSE = [Error Message].

                                    1 2 Previous Next