Jump to content


Photo

Operator AND/OR not working in Column Transformation, PHX1.3


  • Please log in to reply
6 replies to this topic

#1 Christian

Christian

    Member

  • Members
  • PipPip
  • 22 posts

Posted 30 October 2014 - 09:54 AM

I wanted to create a new variable which sets a flag based on data in another column. I used a  custom Column Transformation workflow (Data Wizard) with an IF statement: 

 

IF Column A > X OR blank, THEN return "1", ELSE "0"

 

In Phoenix the syntax is

 

if((A>2) | IsNull(A), 1, 0)

 

However Phoenix only executes the first condition, (A>2) in this case.

 

I had to do 3 consecutive Column Transformations to obtain the desired result:

The first two create intermediate result columns and each executes one condition, the third one combines the 2 intermediate columns into the final one. I heard from Simon that this is fixed in version 1.4, i.e. the operators should work.

 

I attach a very simple example which shows the principle.

 

This may be useful if you intend to flag e.g. AUCinf values from NCA where the extrapolated part exceeds 20% and you have to provide your results in the long thin format (NCA Final Parameters structure). You can't do that immediately on the Final Parameters worksheet, you first have to filter the pivoted worksheet using the condition (e.g. >20%) and then Append (you may have to append one of the filtered worksheets twice, once including the columns where the conditions applies and once without). Then you apply the Column Transformations and stack the final worksheet omitting the intermediate results. 

 

Christian

Attached Files



#2 Ana Henry

Ana Henry

    Advanced Member

  • Members
  • PipPipPip
  • 220 posts

Posted 30 October 2014 - 07:32 PM

I wanted to create a new variable which sets a flag based on data in another column. I used a  custom Column Transformation workflow (Data Wizard) with an IF statement: 

 

IF Column A > X OR blank, THEN return "1", ELSE "0"

 

In Phoenix the syntax is

 

if((A>2) | IsNull(A), 1, 0)

 

However Phoenix only executes the first condition, (A>2) in this case....

Hi Christian,

Why not use embedded if statements and accomplish this in one step?

 

if(isNull(A), 1, if(A>2,1,0))

 

Ana Henry


Edited by Simon Davis, 31 October 2014 - 03:13 PM.
cut full quote


#3 Christian

Christian

    Member

  • Members
  • PipPip
  • 22 posts

Posted 31 October 2014 - 07:08 AM

Ah thanks, that works. Didn't know that embedding is possible here.



#4 Steve Eppler

Steve Eppler

    Newbie

  • Members
  • Pip
  • 2 posts

Posted 04 February 2015 - 11:41 PM

Hi Ana,

 

I have the same issue as the OP but I am not able to use the workaround you described in your earlier post.

 

I want to assign a flag (either 1 or 2) for two different time ranges and 0 otherwise.  Time up to 24 I want to assign 1, and the range of times 336-360 I want to assign 2.  I have many other time values around these ranges that I want to assign a 0.

 

I would use: if(time <= 24, 1, if(time >= 336 & time <= 360, 2, 0))

 

Like the opening post says, the transformation can evaluate the first part, but not the second part (I get 1 in  my dataset but not 0 or 2).  Since I want to transform a range of values, I think I have to use the '&' in my if statement, but since it's not working as expected, I'm not sure how to proceed.

 

Any help you could provide would be appreciated.



#5 Simon Davis

Simon Davis

    Advanced Member

  • Administrators
  • 1,109 posts

Posted 06 February 2015 - 05:00 PM

Hi Steve, I think your requirement is unnecessarily complex, if you use;

 

if(time <= 24, 1, if(time >= 336 , 2, 0))

 

it will get what you want since;

 

if less than or equal to 24 =1

 

ELSE if greater than or equal to 336 flag =2

 

else it's flagged as 0.

 

  Simon

Attached Thumbnails

  • flagged.jpg

Edited by Simon Davis, 06 February 2015 - 05:58 PM.


#6 Simon Davis

Simon Davis

    Advanced Member

  • Administrators
  • 1,109 posts

Posted 06 February 2015 - 06:07 PM

Sorry Steve, I misread your mail, I see you have to flag it back to 0 again after 360.  It's a little bit more involved but you should find this will work;

 

if(time <= 24, 1, if(time >= 360, 0,if(time < 336, 0,2)))

 

  Simon

 

PS I will look into how we can support this more elegantly going forward with Product management.

 

Edit - logged as QC 14881

Attached Thumbnails

  • flagged.jpg

Edited by Simon Davis, 06 February 2015 - 10:25 PM.
adding logged as QC 14881


#7 Steve Eppler

Steve Eppler

    Newbie

  • Members
  • Pip
  • 2 posts

Posted 06 February 2015 - 06:52 PM

Thank you for your reply.  That helps a lot!

 

Sorry Steve, I misread your mail, I see you have to flag it back to 0 again after 360.  It's a little bit more involved but you should find this will work;

 

if(time <= 24, 1, if(time >= 360, 0,if(time < 336, 0,2)))

 

  Simon

 

PS I will look into how we can support this more elegantly going forward with Product management.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users