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

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!

Workflow

Accepted answers

danilang

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

EnsureAtLeastOneRecord.yxmd

atcodedog05

Hi @AndyHH

You missed out the filter tool from @danilang workflow please add it and it should work.

Hope this helps : )

All comments

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

Hi @AndyHH

With records in the input

Without records

Dan

EnsureAtLeastOneRecord.yxmd

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.

AndyHH

Hi @danilang and thanks everyone

Thanks for your solution and I can generate the 0 now. However, my next step is, if there's value, i need to use my own value to ABS() to turn into positive. And if there's no value, i will want to use the generated '0' and also make it ABS().

Is there any path or formula to use? In my mind now is 1.See if value -> yes then ABS. 2. If no value -> '0' then ABS. And i will only need one figures for further usage. Many Thanks!!!

atcodedog05

Hi @AndyHH

Can you show me whats in this formula that's what might be creating the issue.

Hope this helps : )

minhdvo

Thanks for sharing the solution. It is perfect.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15879

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703