Help us build a better product, earn the PaxPro Advocate badge: Six short questions, one cool badge. |
Visit the official Paxata Documentation portal for all of your doc needs.
How to handle nested functions in Paxata?
JRood
Posts: 10 ✭
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!
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!
Tagged:
0
Best Answers
-
Steve
Posts: 17 mod
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(@[email protected] ="Hot",OR(@[email protected] ="Rejected", @[email protected] ="Unassigned")),"Good", "Bad")
I hope this helps.10 -
Steve
Posts: 17 mod
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(@[email protected], @[email protected], @[email protected], @[email protected] 
5 -
JRood
Posts: 10 ✭
That makes excellent sense. Thank you! Still learning all the different options.0
Answers
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.