Dear All,
I have the data as follows and whatever I tried I can't make the new formula column a numeric one.
Could someone help please?
many thanks in advance
Your decimal place separator is a comma, you may need to change it to a full stop for num() to work. I have to, being in the UK. Your local preferences may allow you to work with the comma.
Also, using substr() will work only if all your parity values have the same number of digits. If the number of digits increases, it will crop the value, and if the number of digits decreases, the substr() will keep some of the USD characters and num() won't work at all. I suggest you use substitute() instead of substr() to replace "USD" by nothing, and replace "," by "."
Saitcopuroglu,
You need to take the "USD" out of the data in the rows. JMP sees this as character data and will not allow you to change to numeric. You will need to a Find and replace or a Recode to try and make the change a little easier on yourself with over 1400 rows.
Best,
Bill
I believe that all you need to do is use the 'Num' command in front of your 'Substr' command and it Neill convert to a numeric:
Num(Substr( your column here, 1,7))
Hope this fixes your problem.
Dave
Surround what you have with num(). That will convert a string to a number.
Thank you, I did it already bu substracting the first 7 digits (which are supposed to be numbers) but cant change the new formula column from char to num
diplays missing data
And if you want to add the currency, just go to column info and add to column property "Units" the value "USD", you will get the Units in brackets after the column Name.
Exactly!
Only changing all the commas to full stops solved the problem on its own with substract function but with substitute function it is safer in case shorter than 5 decimals displayed data (1.50000 »» 1.5).
Many many thanks to all input owners for your support ! (And I will change my local preferences from UK to US)
Not to beat a dead horse but you can still use the Num(Substr(….) option by doing the following.
thank you dlee0416, could you please send the jmp data table? I am not familiar with Munger function and additional knowledge is always welcome. many thanks
JSL Character String Functions
Here’s a simple table with the formula in the second column illustrating the Munger function.
Saitcopuroglu, you can use substitute() to replace any string you want (and special characters are strings). You can have multiple substitutes "inside" each other, with each substituting a special character by a space " ", or nothing "" or whatever you like "really anything".
Then there are separate character functions to remove whitespace and turn to lowercase.
The JMP help (F1) for Character functions is really good to see what can be done; you may want to have a look.
You will find the formula of the last column pasted below the picture so you can re-use it easily. Add more Substitutes() as required to remove other special characters I have missed out.
Lowercase(Collapse Whitespace(Substitute(Substitute(Substitute(Substitute(Substitute(Substitute(:Comment, "!", " "), "+", " "), "%", " "), "_", " "), "-", " "), "?", " ")))
To get a slightly simpler formula, use multiple arguments in Substitute().
For example:
Substitute(:Comment, "!", " ", "+", " ", "%", " ", "_", " ", "-", " ", "?", " ");
Thank you MS,
Thank you jvillaumie,
Both work very well !
I am continuously checking the tutorials, books, help, webcasts but sometimes when it comes to action some very simple thing is missing. In this example I knew the Substitute function but did not know it could be surrounded by another Substitute and another and another and by collapse whitespace and lowercase functions...
Many many thanks again, it saved me 8 minutes for each day of my rest life...
Dear all,
again to same topic this time I can not create a formula column which divides the first number by second number (4/5, 44./5, 7.3/10...etc...)
Substraction does not work cos no equal lenght, Substitute is not a way to go Words can not seperate the characters....
many thanks!
I have not read the whole thread. But perhaps this is close (if you can depend on the 'divide' delimiter):
New Table( "Convert Char Fraction",
Add Rows( 4 ),
New Column( "Char Fraction",
Character,
"Nominal",
Set Values( {"4 / 5", "3/2", "27/ 26", "122 /37.6"} )
),
New Column( "Result",
Numeric,
"Continuous",
Format( "Fixed Dec", 12, 3 ),
Formula(
Num( Trim( Word( 1, :Char Fraction, "/" ) ) ) /
Num( Trim( Word( 2, :Char Fraction, "/" ) ) )
)
);
You should be able to use the following formula:
Num(Word(1,:Score, "/"))/Num(Word(1,:Score, "/"))
Many thanks, both work.
Given all the questions, and the length of this post. I'm just going to let you know, it'd be worth the hour of your time to study regular expressions. Regular expressions are a very common programming technique that is universal and can be found in almost all programming languages. Regular expressions can do everything above and more.
The JSL Scripting guide has a very good subsection that should cover all you need to know about the regex() function. And there are plenty of YouTube videos that go in depth as well.
