Several value types in same column

[MRKoura]

Hello KNIME Community,

I’m currently facing an issue that for me looks very hard with no resolution. the attached image is for a daily report that I extract and the outcome is originally color coded, but in KNIME, I don’t see color coding nodes. so I want to convert the content of cells to text format.

• if the cell has a date, it means that this is the date on which the task was “Completed”
• if the cell has a positive number, then this means that task is “In Progress”
• if the cell has a negative number, then this means that Task is “Late”
• if the cell has no value and empty, then this means that task is “Not Opened”

I have attached a screen shot from the last table in outcome from the the model and wish someone can help me out!!

[izaychik63]

Here’re nodes you need to the task

Rule Engine — NodePit

XLS Coloured Writer — NodePit

[MRKoura]

Hi - Thank you for replying fast on this

The rule engine isn’t the best solution as this needs to be an overall application over the whole report, but rule engine is taking it column by column.

For the XLS Coloured node, I don’t have it.

Any other solutions please?

[scapuzzi]

I don’t think you have described your problem with accurate enough detail to get the exact solution you are looking for.

Do you want to overwrite the contents of a cell with either “Completed”, “In Progress”, “Late”, or “Not Opened”?

Do you want to do so over EVERY column in your dataset, not just the one column you highlighted?

Could you provide an example dataset so that others can help you come a solution?

Additionally, if you don’t have the node, you can install it from “http://update.knime.com/community-contributions/trusted/4.1”

[andrejz]

Hi,

I think @izaychik63 is right the “Rule engine” is the best node.
Try:

$Final Artwork…$ < 0 => “Late”
$Final Artwork…$ > 0 => “Progress”
MISSING $Final Artwork…$ => “Not opened”
TRUE => "Completed”

[scapuzzi]

Agree with @andrejz. But if you need to do this over every column, you will need to a column loop and some flow variable selection.

[HansS]

or use the Rule Engine in a loop like this from the Hub.
gr Hans

[MRKoura]

The rule somehow worked, but the part with the completed rule wasn’t captured in the below screen shot, and it was stated as in progress
Any ideas how to over come this issue?

[andrejz]

Hi,

With dates try: $Setup…$ LIKE “???-??-??” => “Finished” …

So the Rule engine node will be

$Setup…$ LIKE “???-??-??” => “Finished”
$Setup…$ < 0 => “Late”
$Setup…$ > 0 => “Progress”
MISSING $Setup…$ => “Not opened”
TRUE => "Check data”

p.s. There must be 4 ? (year) in LIKE statement…In the reply I write 4 but in the preview are are only 3

[MRKoura]

@andrejz This did work perfectly!! thank you

Now, how can I make the Loop to run this same thing over all the columns? because I tried the node “Column list loop start” but I’m not familiar with how to use and what are the steps to follow.

[MRKoura]

Hi @HansS - Can you be able to help me how to make the loop have the rule engine node runs across all columns at once instead of going column by column?

[andrejz]

Hi,

Here I have some problems.
Rule engine node does not accept flow variable for MISSING so we have to make a workaround by filtering missing values (this is solved).
The second problem is that with column name as flow variable the rule engine node in the loop does not give the same result as the rule engine outside the loop (without flow variables).

I can not figure out where is the problem. Maybe someone else can help.

Here is the workflow: Column loop.knwf (18.1 KB)

[andrejz]

Hi,

I still do not know what is wrong with Rule engine in the loop with flow variable, but I have made another solution for this particular case which you can try.and upgrade if necessary
Column loop II.knwf (51.0 KB)

Hope it helps in some way.

[ipazin]

Hello there!

For such cases following approach is mostly ok:

1. use Unpivoting to have all columns in one column
2. apply rules
3. use Pivoting to have your original table structure

Here is workflow.
Column loop II_ipazin.knwf (69.7 KB)

@andrejz

The second problem is that with column name as flow variable the rule engine node in the loop does not give the same result as the rule engine outside the loop (without flow variables).

Flow variable holding column name doesn’t reference column values in Rule Engine node.
See here:

Basic Question about Column Looping and string manipulation

Hi, I’m trying to clean up some text in an excel file. I can use string manipulation to replace “\r” for any single column in the file. I’m having trouble using column list loop start/end and loop variables to make the manipulations on every column in the file. I’m choosing to replace column, so simply replacing the column name in the serial flow with the $${ScurrentColumnName}$$ in the loop results in a file where every row value is replaced by the column name. I’m missing some basic understand…

And check this workflow:

Looping over all columns and manipulation of each – christian.dietz

Br,
Ivan

[MRKoura]

@ipazin - This was brilliant!! it did work lie a charm, thank you so much