We're excited to announce that we'll be partnering with Credly starting October 19th - see what this means and read the announcement blog here!

Alteryx Designer Discussions

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

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

AndyHH
6 - Meteoroid

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!

 

2021-10-06_18-28-16.png

 

2021-10-06_18-47-18.png

14 REPLIES 14
atcodedog05
20 - Arcturus

Hi @AndyHH 

 

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

pdave87
11 - Bolide

@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
11 - Bolide

@AndyHH sharing screen-prints for reference

pdave87_0-1633518165988.png

 

 

pdave87_1-1633518214112.png

 

 

DawnDuong
12 - Quasar
12 - Quasar

hi @AndyHH 

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

Dawn.

Nanoq
8 - Asteroid

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
18 - Pollux
18 - Pollux

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.  

 

danilang_1-1633520720257.png

 

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

danilang_3-1633520996987.png

 

Without records

danilang_2-1633520951075.png

Dan

 

DawnDuong
12 - Quasar
12 - Quasar

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
6 - Meteoroid

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
20 - Arcturus

Hi @AndyHH 

 

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

Labels