Column Expression - not working

[AAM]

I am doing a data clean up on tables.

Any CPAct_Name with a CPAGroup = SUR is copied into the SurgTherapy column.
Any CPAct_Name with a CPAGroup = HOR is copied into the HormoneTherapy column.

All is going well, except for the one transform in red! I want to change
“Leuprorelin 22.5 mg IM(lucrin) Q3 Months” into “LHRHagonist”, but it just won’t work. All of the other transformations do work in Column Expressions.

Here is my Column Expressions code:

if (or(column(“CPAGroup”)=='HOR ', column(“CPAGroup”)=='DEG '))
{
string(column(“CPAct_Name”));
}
else
{
string(" ");
}

I am writing the result of this equation out as a string variable into the new field called HormoneTherapy, however for this particular entry, the field produced is empty. Interestingly, the same think happens when trying to a Text replace (Dictionary).

I think that there must be something in the text entry (size?, the period?) which is causing a problem, but I can’t find anything to help resolve the dilemma. Interestingly, other entries containing ‘*’, ‘(’, ‘)’ and ‘.’ work. Any help would be gratefully received again.

A

[takbb]

@AAM

if (or(column(“CPAGroup”)=='HOR ', column(“CPAGroup”)=='DEG '))
{
string(column(“CPAct_Name”));
}
else
{
string(" ");
}

Hi @AAM ,

You appear to have included a space at the end of the 'HOR ’ and 'DEG ’ used in your comparison. Does your data have a space at the end? If not, you need to replace the first line with

if (or(column("CPAGroup")=='HOR', column("CPAGroup")=='DEG'))
instead of
if (or(column("CPAGroup")=='HOR ', column("CPAGroup")=='DEG '))

Bu given that you say it works for some lines, then probably some of your data DOES have a space at the end (and so works with your expression) then you could adapt the expression as follows to strip spaces from the end of the column before comparison:

if (or(stripEnd(column("CPAGroup"))=='HOR',stripEnd(column("CPAGroup")=='DEG')))

If that still doesn’t work, it is likely something else in your data, in which case could you upload a copy of the data that you have in the screenshot (copy and paste just that data, so there is nothing sensitive) into a new excel doc and upload that.

The specific part of your question…

All is going well, except for the one transform in red! I want to change
“Leuprorelin 22.5 mg IM(lucrin) Q3 Months ” into “LHRHagonist ”, but it just won’t work.

I’m not sure from the column expression and the data shown where your would derive the LHRHagonist from. Have I missed something?

[AAM]

Leuprorelin is a LHRH agonist with multiple brand names like Zoladex, Lucrin and Eligard.

There is a space after HOR. And the expression works for all other cases. I’ll try the strip command tomorrow. And I will prepare a XLS.

A

[bruno29a]

Hi @AAM , as @takbb pointed out, we can’t see where you are doing the change from “Leuprorelin 22.5 mg IM(lucrin) Q3 Months ” to “LHRHagonist ”.

Is the change that failed, or it’s not filtering the line at all (hence the issue with 'HOR' vs 'HOR ', which the stripEnd() function should take care of)?

For the filtering part, you can do a quick check with a Rule-based Row Filter with the following rule and see if the line qualifies:
$CPAGroup$ = "HOR " => TRUE

[AAM]

Oh sorry, I see what you are saying now! Sorry for confusing you.

I was attempting to make the change to “LHRHagonost” once the entry is transferred into the second column (the column where the red box is). However the “leuprorelin” doesn’t transfer across. Even if I try to do the manipulation without transfer it still comes out blank.

A