community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

IF or IFF statement to return null record to 0

Atom

Hello Designers,

 

How can I replace the not found text line to a numeric 0 in order for the total debt calculation formula to run?

 

The formulas is this:

Total Debt = [ST_Loans_Payable_Bank]+ [ST_Loans_Payable_Other]+[CPLTD_Bank]

 

The input file below does not have a field containing [CPLTD_Bank]. What tools I can use to return this not existing field to 0 in order for the total debt calculation to run?

Current Liabilities10/31/2015
ST Loans Payable-Bank243,803
ST Loans Payable-Other200,000
CPLTD-Other Secured0
CPLTD-Other Unsecured0

 

The total debt calculation formula cannot be changed. However, the input files all look different as they do not contain all the fields presented in the formula. Any help would be greatly appreciate.

 

Alteryx
Alteryx

If the column is already in the dataset, I would configure a formula tool this way and change the datatype with a select tool after:

 

Image1.PNG

 

If the column does not exist already, and the value should always be 0, you can simply configure a formula tool this way:

 

Image2.PNG

 

Let me know if I missed the mark here, and I'll continue to help you out with further clarification of the goal. And if you have questions let me know!

 

Luke

Hello Karen,


Why don't you just create a new column using a Formula tool with that name before creating this new field? This way, you will have that column and Alteryx won't throw an error.

If you add that column to the expression, you need to have that field available, otherwise it won't work.

 

I hope it helps.

 

Felipe Vilela 

Atom

Thanks Luke! The two scenarios you listed below are actually where the challenge is. I do not know if the column is already in the input files or not.Data set #1 has this column already, but Data set #2 does not. If the filed is not already in the dataset, the tool has to return a 0; If the field is in the dataset, the tool has to return the true value. Is there a tool that resolves the two scenarios? 

 

Thank you!

Atom

Thanks Felipe! I think the real issue is that I do not know if the column is already in the input files or not. Data set #1 has this column already, but Data set #2 does not. If the filed is not already in the dataset, the tool has to return a 0; If the field is in the dataset, the tool has to return the true value. Is there a tool that resolves the two scenarios? 

Alteryx
Alteryx

Hey @karen88  I think I understand now.

 

Have you tried combining the datasets with a union tool to create one larger one. This will include the values in the column if they are present. If they are not, they will be null values, and can be replaced by using a conditional IF statement.

 

Let me know if you have any other questions or need clarification!

Luke

 

Atom

Hi Luke - this is a great idea, but the two data sets cannot be simply combined.The reason is that each data set includes the financials from one customer. This total debt calculation need to run separately for each customer. For example, Total Debt = Field 1 + Field 2 + Field 3. Customer A does not have Field 1. Customer B does not have Field 2. How to get to a result that for Customer A, the missing Field 1 returns a 0, and the formula calculates Total Debt as 0 + Field 2 + Field 3. For Customer B, the missing Field 2 returns a 0, the the formula calculates Total Debt as Field 1 + 0+ Field 3?

Bolide

Hi @karen88 

 

If the columns are always named the same, then you can use a text input as a master template for your columns.  Attached is a simple workflow showing where data columns are missing from inputs and how the master template provides the framework.

Labels