1 2 Previous Next 15 Replies Latest reply on Sep 2, 2016 9:21 AM by David Li

    Data Stored as Text Need Help

    Jon Townsend

      I currently have a data set stored in a Microsoft SQL server, and I only have read access. There is a revenue number that is stored as text (e.g. $3.15). I have created an extract on my local machine to make it a little more accessible and avoid issues I was having with calculated fields.

       

      Unfortunately I still can't convert it to a number through the normal drop down, so I have been trying to do so using a calculated field, but I can't use float because it contains different characters ($) and I can't use Int because then I lose my decimals which I need for this project.

       

      Has anyone had this problem before, and how did you solve it?

        • 1. Re: Data Stored as Text Need Help
          chris.moore.11

          Is it just the "$" symobol that is non-numeric, and is it always the first character?

           

          If so you could do      float(right([field], len([field])-1))

          1 of 1 people found this helpful
          • 2. Re: Data Stored as Text Need Help
            Kathryn Bridges

            Hey Jon,

             

            Not sure this will work, but have you tried MID([Field],2,n) to get rid of the dollar sign, and then either concatenating with other variations of RIGHT([Field],2), or float once you've gotten rid of the $?

             

            Also wondering if you could just do Int[Field]/100 to get the decimals back?

             

            Good luck!

            1 of 1 people found this helpful
            • 3. Re: Data Stored as Text Need Help
              David Li

              Try FLOAT(REPLACE([Field], "$", "")).

              1 of 1 people found this helpful
              • 4. Re: Data Stored as Text Need Help
                Jon Townsend

                Thank you both for the advice. Actually either solution would work, I tested both with a sample data set, but unfortunately it isn't working in my actual data set.

                 

                It returns all null values. So I broke down your concept Chris into stages of separate fields :

                 

                Calc1. LEN([Field])

                Calc2. Calc1-1

                Calc3: (ETC....)

                 

                but for some reason when I do LEN([Field]) its returning weird values.

                 

                $2.17 shows a length of 6, but when I copy and paste the value into Excel it says the length is 5 which should be correct and it pastes in directly as a number instead of text.

                 

                See the image below for clarity.

                 

                Any thoughts? I have tested tableau 9.3 and tableau 10 to make sure it wasn't a Tableau 10 glitch. Capture.PNG

                • 5. Re: Data Stored as Text Need Help
                  Mahfooj Khan

                  You can try this

                  FLOAT(MID([LTM Avg], 2))

                  Right click that calculated field in the data window and select "Default Properties" --> "Number Format" and select Currency (or Currency Custom) and just remove the prefix. Then drag the field in measures pane.

                   

                  Let me know If this help.

                   

                  Mahfooj

                  • 6. Re: Data Stored as Text Need Help
                    kettan

                    $2.17 shows a length of 6, but when I copy and paste the value into Excel it says the length is 5 which should be correct and it pastes in directly as a number instead of text.

                    This calculation may help us identify the mysterious 6th character:

                     

                    STR(ASCII(LEFT([Field],1)))  + "  " +

                    LEFT([Field],2) + "  ...  " +

                    RIGHT([Field],2)  + "  " +

                    STR(ASCII(RIGHT([Field],1)))

                     

                    Could you share a screenshot of it in use?

                     

                    Ps. My intention is not to use much time on the community today, but just thought to share this.

                     

                    1 of 1 people found this helpful
                    • 7. Re: Data Stored as Text Need Help
                      chris.moore.11

                      Yeah, if there's a mystery character somewhere id breakdown each character and see what it is.

                       

                      left([field],1)

                      right(left([field],2),1)

                      right(left([field],3),1)

                      right(left([field],4),1)

                      right(left([field],5),1)

                      right([field],1)

                      1 of 1 people found this helpful
                      • 8. Re: Data Stored as Text Need Help
                        Jon Townsend

                        Thanks Chris. This was helpful. I got it working based on your original input, but for whatever reason I had to go to an older extract of the database that I had from 2 weeks ago. The data is the exact same so it doesn't make a difference that its two weeks old, but there is something going on with the SQL that that team is going to have to look into.

                        • 9. Re: Data Stored as Text Need Help
                          Jon Townsend

                          Thank you Kettan,

                           

                          I tested this formula as well. you wrote it perfectly. It definitely helped me sort out what the out of place character was. it is a space that is being stored before the "$". See the image below per your request to see it in use.

                           

                          Thanks again. Cheers.

                           

                          Kettan Method.PNG

                          • 10. Re: Data Stored as Text Need Help
                            Jon Townsend

                            To sum up the answer to this question for future readers:

                             

                            FLOAT(RIGHT([FIELD],LEN([FIELD])-2)) solved the problem. The number 2 in the formula is the variable if you don't know how many characters there are. Maybe more or less than that. In my data set there was a ghost space (" ") in front of a dollar sign that was causing the problem. The method below shows how to determine what and where it is.

                             

                            Originally I was under the impression that there was a spare character on either end, but Using Kettan's suggested method above I was able to determine where and what the character was. His formula is as follows:

                             

                            STR(ASCII(LEFT([FIELD],1)))+" "+

                            LEFT([FIELD],2)+"..."+

                            RIGHT([FIELD],2)+" "+

                            STR(ASCII(RIGHT([FIELD],1)))

                            • 11. Re: Data Stored as Text Need Help
                              kettan

                              You are welcome 

                               

                              I am a little bit surprised, because I had assumed  David's  formula in  Re: Data Stored as Text Need Help  didn't work while at the same time knowing that it works fine with a prefixed space or for that sake a multitude of prefixed and suffixed spaces.

                               

                              Could you try David's formula again and confirm that it doesn't work?

                               

                              FLOAT(REPLACE([Field], "$", ""))

                               

                              • 12. Re: Data Stored as Text Need Help
                                Jon Townsend

                                For whatever reason (possibly user error...) the prefixed space was giving me problems when I used David's formula. So using yours showed me the prefixed space which made the difference.

                                • 13. Re: Data Stored as Text Need Help
                                  David Li

                                  Hmm, interesting. Does it work if you add TRIM() inside the FLOAT() formula?

                                  • 14. Re: Data Stored as Text Need Help
                                    Jon Townsend

                                    Nope. didn't like the TRIM(). Not sure why, that works logically...

                                    1 2 Previous Next