There are instances when columns can take on an unknown typecast denoted by a ? Icon next to the column name. This often happens after transposes or unpivoting. I am unsure why this happens, if a column contains numbers as well as text surely it should be denoted as string typecast.
handling these unknown column types in subsequent nodes is tricky. I would have hoped that there would be nodes to easily convert these, but that doesn't seem the case.
is it possible to have a node like string to number node, which changes typecast, but obviously have ones called unknown typecast to string, and unknown typecast to double.
I think this is doable. Have you tried using the column rename node to cast the mystery columns as a string, then String to Number to convert them to doubles?
Firstly, this is painful when column names change names, as you have to redo it all over again in the rename node.
The advantage of the proposal, is that such a node would only display the "Unknown" column types in the include box in the node, and you can use the force inclusion/exclusion protocols to select what columns would change type. This way it can be set up in an automated fashion.
Additionally, the Rename node never seems to change the typecast properly. If you change a column to string for instance. If you then look at its column type description, it is not "String" but "Non-native", and this also causes problems in some nodes. Ideally, it should change it precisely to the typecast desired, not to this "non-native" type.
I see what you mean now. With dynamic column name, this is not very easy. I have gone ahead and logged this request in our tracking system. We will let you know when we have a better solution for you.
How do you currently deal with this situation? I have a Transpose node that will correctly cast the columns to doubles 90% of the time. It's the 10% that I cannot find a way to deal with.
I just solved the problem by avoiding that the type is changed to UNKNOWN during transpose by creating a column i.e. with STRINGS before the transpose action. The type will be automatically taken from it during transpose. I simply remove the new row again after transposing.
yes, in my case I added a column with strings (I used the RowID node to transfer the header strings to the newly inserted column). However, I guess numbers would also work. Just that Knime can automatically assign a type to the transposed columns. The type will stay even when the table is empty after the additional row is deleted again.
Still a workaround, I know, but for me the Auto Type Cast node mentioned above did not work since I have a changing number of columns.
I also met the same challenge recently and find this post.
However, I want to share another method to solve.
Yes, it will happen most after unpivoting the columns, if the columns are more than two different type ( e.g. string for column A and data for column B), after the unpivoting, the new column will show a question mark and can not manipulate for string or data operation afterwards. Especially in a loop.
So my way is add one String to Number node before Unpivoting Node. In the configuration node “Enforce Exclusion” to keep the columns that not for unpivoting. So for the rest column that we want to do unpivoting, if they are not typo of number, will automatically appear in the “Include” Tab, then convert to number.
Especially in a loop, we are not sure which column is text or number, so we can use this way to convert into number.
@kevinchen Thanks so much. Today I encountered with this issue of unknown type columns too. In my case, the issue arises when Concatenating string rows with that of number rows. I followed your solution, but in my case I did it the other way around, which was to convert the Numbers to String. The result was that all my columns are now in String type after Concatenating.
The only minor irritation is that all integer values now have been appended with decimals of point-zero (.0) for unknown reasons. Anyways, cheers!
Update: I was working on another problem which leads me to this node I’ve never encountered before.
It is the Column Auto Type Cast Node.
It’s able to convert all the Unknown Type columns without us having to alter the upstream tasks. Hope this helps to whoever’s having issues in the future.