[Urgent]If no data/column, then create "0" into it

[AndyHH]

Hi Community!

I recently want to build a model which after combining two excels, if there's no data, i want to create "0" into the column, so that i can do calculation after this path. And most of the time it will have data so i can calculate directly, but if it didn't, I need to generate "0" on my own. Can anyone help for this?

I've tried the formula tool and type : if isempty([Sum_Amount]) then '0' Else [Sum_Amount] ENDIF. But it doesn't work.

How can i use other tool or how do i type it in the formula? Many thanks!

[atcodedog05]

Hi @AndyHH 

Can you provide some sample input and expected output it will help us get a better understanding of the usecase.

[pdave87]

@AndyHH hi, check data type of the column where you want to replace null() with '0' as a string. If it's numeric then we do encounter an error.

[pdave87]

@AndyHH sharing screen-prints for reference

[DawnDuong]

hi @AndyHH 

Have you tried the Impute Tool? https://help.alteryx.com/20213/designer/imputation-tool

Dawn.

[Nanoq]

i assume the column will be there in the excel even if its empty, if not, make a text input with ONLY the coulmn names, no data, that will ensure that an empty column appears after the union. 
Use the select tool to force the coulmns to be a number format ( int, float or double), the use the data cleaning tool to turn empty and null values into 0
Alternativeley it actually looks like youre getting an output without any rows in it from the union - any amount of "empty" cant fix that. if thats the case, use the trick with the text input from above, and just make a single row of data that you know you can either discard later, or that you know wont affect the calculation - since youre doing a sum, a "0" might be a good way to go, but i cant say for sure without seeing the workflow

[danilang · Accepted solution]

Hi @AndyHH 

In your case, use the Count Records tool.  It's unique in that it's only summary tool that always returns 1 record, even if there are no records in the input.  After the Count Records, change the field name to match the input and add a filter to only pass the 0 count record.  Union this with your incoming steam.  

With this method you either get all the records from your input OR a single record with 0.  Change the N=100 value in the Test Sample tool to 0 to see the result if you have a least 1 record.  Don't include this in your solution

With records in the input

Without records

Dan

[DawnDuong]

hi @AndyHH 

I think the issue is the data type. You want to fill in a numeric 0, not a string 0, right?

In that case, just remove the inverted comma from your formula and make sure the field is set to a numeric data type.

Dawn.

[AndyHH]

Hi  @atcodedog05

For example, I will use 'filter' first to find out those columns with description i needed. But if the column doesn't include the content I need, it will become no data on it. So when this point, i will need to create a column then put '0' into it so that i can do further. Thanks.

[atcodedog05]

Hi @AndyHH 

You can refer to @danilang post that should help 🙂.