Create new column with IF Statement

[skeen503979]

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?

[Jonathan-Sherman · Accepted solution]

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

[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?

[Jonathan-Sherman]

Hi @skeen503979,

You can't upload an Alteryx analytic app (.yxwz) however you can package the workflow to create a .yxzp and attach that.

Go to Options -> Export Workflow

Regards,

Jonathan

[skeen503979]

@Jonathan-Sherman 

Thx, this should work.

[skeen503979]

@Jonathan-Sherman 

I uploaded the workflow.  Can you tell why it is producing millions of rows of blanks?  

[Jonathan-Sherman]

Hi @skeen503979,

In your bottom data set you have 40,616 Null records:

In your top data set you have 754 Null records:

When your workflow tries to join the Null SKU value, it joins it to every Null SKU record in your other data set, therefore you end up with 40616 * 754 records with just null values in. This means you end up with an extra 30,624,464 records with just null values.

If we remove the nulls from both data sets before joining we end up with just over 31k records.

If this has solved your issue please mark the answer as correct, If not let me know!

Regards,

Jonathan