Now that it's live, don't forget to accept your certification badge on Credly today! Learn more here.
We are currently experiencing an issue with Email verification at this time and working towards a solution. Should you encounter this issue, please click on the "Send Verification Button" a second time and the request should go through. If the issue still persists for you, please email support@alteryx.com for assistance.

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
7 - Meteor

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
7 - Meteor

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