Hi!
Is there a method to convert certain rows in a column to number and if the the row contains letters (not number) to leave as string?
Hey and welcome to the forum!
In general each column can only have one data type. If you have a mix of strings and numbers that will lead to your numbers displaying as string.
There are some nodes and some ways how I think you could still perform calculations on rows that contain numbers, however not that easy and straight forward as you may have to chain multiple formulas in e.g. column expressions or expressions node to check if the cell content can be parsed to a number, if so apply your logic, then convert back to string, handle the scenario where it is not a number etc…
Can you share more about your specific usecase? Then it might be easier to help you find a good solution!
3 Likes
I think you are looking for something like this KNIME_project.knwf (20.8 KB)
gr. Hans
2 Likes
Hi @Emilychan112 , welcome to the KNIME community.
You cannot convert just specific rows in a column to a number whilst leaving other rows as string, as an entire column must and will always be the same data type.
If it helps, what you can do is use String to Number node and have it create a new column based on the original column, so that this new column contains only numbers where they can be determined. Make sure you don’t set it to “fail on error” and it will just warn (with a yellow triangle) if some strings could not be converted. There isn’t an option to append a new column in this node, so you can append the converted columns back onto the original table as shown here, where String to Number has been applied to “column1”:
4 Likes
Thank you for all your replies!
Let me know if this makes sense- is it possible to split the column to cells with numbers only convert that to number and a second column that contains letters and numbers and leave that as a string. And use a column appender to combine the column together?
My original issue is doing a vlookup - however certain values aren’t showing up because they needed to be converted to number
I think this is what @HansS is doing - that said if you mean by “appending” to bring all values back together in one column where “letters” are treated as strings, and “numbers” as integer or double, this will not work given that there can only be one data type per column.
If you look at the example from HansS you could take it a step further and change column1 to “missing” (red question mark) if the value in the same row in column1 (#1) is not missing.
Then you have one column that is only those entries that are not numbers, and one with numbers.
Can you provide a visual of how HansS was able to change the missing red question marks to equal the value of the same row?
I think you can just download this example WF here - he used the rule engine node to copy and then the String to Number to try for each row in to turn it into an a number. Where that was not possible you get the missing value.
You can also use Expressions node to do this:
Have added that node to what Hans did:
KNIME_project.knwf (80.5 KB)
I also note that Hans actually managed to get them all into one column, also the data type is now ? which may be a problem depending on your use case…
1 Like
also the data type is now ? which may be a problem depending on your use case…
This can be solved by the
gr. Hans
1 Like
with the issue that the data type is now? - I tried using the column auto type cast but the configuration i only see -choose a date format and it is still giving ? for the data type
Don’t change anything in the configuration of the node. Just run it.That will do thrick and convert the ? Into string.
1 Like
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.