If clause inside String Manipulation (Multi column)

[jricgar]

Hello all,

Anyone here could help me with this error?

I have been looking for the correct format of the if clause for an hour! What the f*** is happening here?

Thanks in advance!

[takbb]

Hi @jricgar , I may be wrong but I don’t think you can do this in String Manipulation. I think that node simply wants to return the value of one or more nested string functions.

For conditional logic, I would suggest using the Column Expressions node instead
e.g.

hope that helps

[jricgar]

Nope, I need a Multi-column node cause I want to do this operation for all (and variable) columns.

Thanks anyway!

[takbb]

Ok, what about Column Expressions inside a Column List Loop?

Terminate with Loop End (Column Append) if you are appending columns

It might give some pointers

[jricgar]

Loop is really inefficient. If I cannot use the Multi-column string manipulation due to code limitations, I think we can modify our workflow to apply this transformation before creating the columns, which result from a pivoting node.

Thanks for your help @takbb!

[takbb]

Yes I agree they are generally inefficient and I try to avoid them too where possible, but how many columns are you looking at? If it’s a relatively small number, I think I’d be inclined to make it work, check its performance and only then worry about further optimisation if really need to, especially if it’s going to complicate things, but you know specifics of your use case better than me, so obviously that’s your call.

Anyways, happy to help and hope you get it working.

[jricgar]

2 thousand columns

Too much for an efficient loop architecture.

[takbb]

Ok fair play… although personally, for anything under 5000, I’d go with hand coding each one of them !

[daniela_digles]

Hi @jricgar,
I’m not sure how the input looks like but maybe it works with a regex replace in the String Manipulation (Multi Column) node. e.g. regexReplace($$CURRENTCOLUMN$$,"[^D]" ,"") replaces all characters that are not D with empty values.

[takbb]

Good idea @daniela_digles .

Just in case there were two D’s in the string, (which would then become “DD” with the regex), this could be wrapped to return only the first character, so extending your suggestion a little, ought to work for the example given:

substr(regexReplace($$CURRENTCOLUMN$$, “[^D]”,"" ),0,1)

[jricgar]

Well, each cell could contain an enumeration of different letters (N,S,A,C,D, each one is a state code) and this enumeration corresponds to all states in a date.

And I need to convert these enumerations into just a letter/state using prioritization in some way. So my first post was just an example of coding because I wanted to nest several if-clauses in a string manipulation multi-column node.

After commenting here, finally I decided to apply this conversion (e.g. “A,D,S,C” to just “C”) before pivoting by those integer values (e.g. ‘28878’, is just an item code) so I was able to use a column expression instead of a string manipulation Multi-column.

But, at the end, I have a big table (3863 rows, 6109 columns) sorted by increasing date where a missing value substitution (keep previous value) has to be applied. This missing node execution never ends. So my workflow doesn’t work.

This is the table where missing node fails:

So finally I had to avoid pivoting and work with a 3 column table (Date, item code, and state code) and approximately 22 million of rows, which at this moment is been processed with a group loop structure by item code, and inside this loop I apply the missing value operation.

Find below both versions of the process, with pivoting and with loop:

Missing value node on pivoted data just gives me a WARNING error, but its execution never end:

WARN Missing Value 6:123 ns missing values.
Column “28540” still contains missing values.
Column “28541” still contains missing values.
…
Column “28849” still contains missing values.
Column “28851” still contains missing values.
Column “28852” still contains missing values.
Column “28859” still contains missing values.
Column “28878” still contains missing values.

The Loop branch is running for an hour…

Any new suggestion?

Thanks in advance!

[jricgar]

Ok. Missing Value Node on pivoting branch worked. It was around an hour but finally the execution of this node ended.

Thank you guys for your help.

[bruno29a]

@jricgar @takbb , in relation to choosing string manipulation multi-column to avoid the loop, maybe the string manipulation multi-column is actually doing a loop in the background?