Column Expression - not working

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

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? :wink:

2 Likes

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

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

2 Likes

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

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.