7 Replies Latest reply on Aug 1, 2018 6:46 AM by Benjamin Hart

    Simple Concatenate Doesn't WORK!

    Benjamin Hart

      Hey All!

       

      I am having this super frustrating issue with Prep. I have 4 columns that I want to concatenate into one: Config = [Rating] + [Rotor] + [HH] + [Hz]. They are all strings, so super easy, right?

       

      Apparently that is a great big nope.

       

      For some reason, when I concatenate, [Config] zeroes out, and so does [HH]. If I remove [HH], it works perfect. Obviously, the issue is with that field.

       

      I have been troubleshooting [HH]. It is a calculated field based on an integer field, [Tower/Runner]. Formula looks like this:

       

      if find(str([Tower/Runner]),".")=0

      then str([Tower/Runner])

      else left(str([Tower/Runner]),find(str([Tower/Runner]),".")+1)

      END

       

      I do this because a few of the values when converted to string, return insanely long decimals, like 68.29999999999999997. I only want 123.4 or 123. The above does that. I then group and replace a few values and Bob's your uncle. Super straight forward.

       

      But, IT DOES NOT CONCATENATE! AHHH! WHY?!

       

      I even tried just converting it to string, like this -- str([Tower/Runner]) -- and it behaves the same.

       

      Can anybody help?

        • 1. Re: Simple Concatenate Doesn't WORK!
          Ankit Bansal

          can you try this:

           

          str(ROUND([[Tower/Runner]],0))

          • 2. Re: Simple Concatenate Doesn't WORK!
            Benjamin Hart

            Yep. Doesn't work.

             

            Applying the str() calc to integers is what causes the weird super long decimals. So, you have to account for that with a formula like mine if you want a string like "123.4" or "123".

            • 3. Re: Simple Concatenate Doesn't WORK!
              Joshua Milligan

              Benjamin,

               

              It looks like you are hitting some of the strange things that can happen when working with floating point values (it happens in Tableau Desktop - Re: IS STR () Rounding Broken? - and really anything that uses floating points).

               

              As to why it isn't concatenating - that is odd.  I could see it potentially not working if the result ended up being NULL, but that doesn't seem to be possible based on your calculation. I've done some testing and cannot replicate the issue.  But here are some things I might suggest to try:

               

              • Wrap your entire HH calculation in a TRIM() function to ensure there are no leading spaces.  Shouldn't be a big deal, but I have run into some cases where Tableau Prep has strange issues with leading spaces.
              • Alternately, do a Clean > Trim Spaces on the HH field prior to the concatenation
              • If you are okay with losing the decimal altogether in your string, try this calculation instead:   SPLIT([Number], ".", 1)
              • Instead of using STR() in a calculation to convert to a string, simply change the data type to String instead.  You might even try this on the Input step to see if you can get Tableau Prep to treat it as a string from the start.
              • Re-write the Config calc as:    IFNULL(TRIM([Rating]), "") +  IFNULL(TRIM([Rotor]), "") + IFNULL(TRIM([HH]), "") + IFNULL(TRIM([Hz]), "")

               

              It doesn't seem like you should have to do any of those to get it to work, but maybe one of those will get past the issue that's causing your headache.

               

              Hoping it helps,

              Joshua

              • 4. Re: Simple Concatenate Doesn't WORK!
                Benjamin Hart

                "Instead of using STR() in a calculation to convert to a string, simply change the data type to String instead.  You might even try this on the Input step to see if you can get Tableau Prep to treat it as a string from the start."

                 

                This one worked. Converted [Tower/Runner] to a STR(), then made then embedded the original [HH] calc in the concatenation. I.e.:

                 

                [Rating]+"-"+[Rotor]+"@"+

                 

                //This gives you HH because the [HH] field is dumb and won't concatenate.

                if find(str([Tower/Runner]),".")=0

                then str([Tower/Runner])

                else left(str([Tower/Runner]),find(str([Tower/Runner]),".")+1)

                END

                 

                +", "+[Hz]

                 

                No idea why it works like this, but it does. Clunky as ****.

                 

                Thanks Joshua Milligan!

                • 5. Re: Simple Concatenate Doesn't WORK!
                  Benjamin Hart

                  This also works...

                   

                  [Rating]+"-"+[Rotor]+"@"+str(int([HH]))+", "+[Hz]

                   

                  What in the exact heck?!

                  • 6. Re: Simple Concatenate Doesn't WORK!
                    Joshua Milligan

                    Benjamin,

                     

                    I'm glad at least one thing (and looks like more than one) worked.

                     

                    i'm pretty sure it's some kind of bug that a slightly different calc or order of steps avoids.  It would probably be worth submitting to support so they can get to the bottom of it and hopefully implement a fix.

                     

                    -Joshua

                    • 7. Re: Simple Concatenate Doesn't WORK!
                      Benjamin Hart

                      Found a WAY better method. I converted the column to STR in the SQL query using VARCHAR(5), then transformed it to the format I want using a couple IF statements. Tried the concatenate and it works like a charm!

                       

                      Now the code is way more dry and the whole thing is much more stable.