How to handle nested functions in Paxata?

[JRood]

Hi everyone.

I'm working on a project where I need to create several new columns using the functions accessed through Compute.  I have hit a stumbling block in that I have to create a column in a way that seems to require two nested functions and I have not been able to do so successfully.  

The main function is IF.  There are two criteria that must be me for the IF, so I'm also using AND, that part has no problem.  However for the second criteria, there are two options out of six in the same column that need to be included.  So I need to have a nested OR.

Here's the equation using the IF AND; this works fine but it's not getting to the category that's being asked for.

If (And (left (column , 3) = "ANN", (column2 = "FINANCIAL INSTITUTIONS")), "ANN-FIW", "N")

This works fine but I also need for the second column to have another accepted value of "REGIONAL WIREHOUSE" along with "FINANCIAL INSTITUTIONS"   It seems like there must be a way to do this but I have met with repeated failures all day yesterday.  Does anyone have suggestions, instruction, or advice?

Thank you!

[Steve · Accepted solution]

Unrelated to Paxata, And/Or logic can be tricky at times.  It is usually helpful to break everything down to individual decisions.

From what you described, I believe you are looking for the following:

If (And (left (column , 3) = "ANN", OR(column2 = "FINANCIAL INSTITUTIONS”, column2 = "REGIONAL WIREHOUSE")), "ANN-FIW", "N”)

Please note, the way that you are putting this calculation together, if "column" doesn't contain "ANN" the rest of the calculation will return false regardless of what is in column2.

Using sample data, here is the equivalent
if(AND(@Rating@ ="Hot",OR(@Status@ ="Rejected", @Status@ ="Unassigned")),"Good", "Bad")




I hope this helps. 

[JRood]

Thank you!  That does help with that issue.  In continuing on, I have come across another hurdle.  I have four columns that I need to create a new column from based on the text in them.  The text isn't the same in any of them, and to be usable for the further work this is for, the fields in the new column have to read the same as the individual ones.

So if Column 1 has ID and N, Column 2 had FW and N, Column 3 has LI and N, Column 4 has RP and N.  I need to make a Column 5 where ID, FW, LI, and RP all appear for the appropriate rows.  Any suggestions?  This one seems like a real doozy, so I get it if it needs to be approached a different way.

[Steve · Accepted solution]

There are many ways to accomplish this.  The way that I would probably go about it would be run a "Find and Replace" all "N" for <blank>.  You can do this across all four columns in one step using the column picker.




Then I would build a calculation using FIRSTNONBLANK(@column1@, @column2@, @column3@, @column4@ )

[JRood · Accepted solution]

That makes excellent sense.  Thank you!  Still learning all the different options.