Create new column with IF Statement

I'm using the Formula tool and want to simply create a new column (called [DEPT_DESC]) and input a name into that field "AIZ Sales" for all rows that contain any data. I just picked the [Serial #] column because each row of my data has that number listed.

What am I doing wrong?

Also, I am getting several thousand rows beneath this with blank data. How can I fix this?

Help

Accepted answers

Jonathan-Sherman

Hi @skeen503979,

Since i didn't know whether an empty cell is null or empty I went for the below, however you could remove one of the IsNull() or IsEmpty() statements depending on how you deal with empty/null cells.

IF IsNull([Serial #]) Or IsEmpty([Serial #]) THEN Null() ELSE "AIZ Sales" ENDIF

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

20200218 - CreateNewColumn.yxmd

All comments

Jonathan-Sherman

Hi @skeen503979,

Since i didn't know whether an empty cell is null or empty I went for the below, however you could remove one of the IsNull() or IsEmpty() statements depending on how you deal with empty/null cells.

IF IsNull([Serial #]) Or IsEmpty([Serial #]) THEN Null() ELSE "AIZ Sales" ENDIF

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

20200218 - CreateNewColumn.yxmd

OllieClarke

Hi @skeen503979 if you want to add the same value for every row in your data, the formula you need is simply

"AIZ Sales"

If you only want that value where [Serial #] is not null, then use this formula

If !ISNULL([Serial #]) THEN "AIZ Sales" ELSE NULL() ENDIF

Hope that helps,

Ollie

skeen503979

@Jonathan-Sherman

Thanks for the help. That fixed my first problem (of giving the data source a department name), but I'm still producing millions of blank/null rows (not sure which vocabulary to use). When I use the Join tool with my 3rd (and last) data source it populates 30 million records when it should only be 69k records. I'm thinking there must be something wrong with my Join tool now that I have renamed some columns in my upstream Formula tools.

I'm trying to attach the workflow so you can see the details, but it keeps saying it's the wrong format, although I know this kind is accepted?

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235