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.
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.
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;
