IsEmpty() and Join Queries

[newbie22]

Hey All,

I am a newbie to Alteryx. I have a table of 21columns and 750 rows.

I need to remove all [null] value rows from all the columns. Join query is not working on it somehow and IsEmpty() function seems to take only one value at a time.

Any suggestions would be welcome.

Thank you.

Meena

[Qiu]

@newbie22 
I can see you already using the "Remove null rows" option in the cleansing tool,

Not working? I can not check much details since the data file is missing from the workflow.
Or if there is only one null in a row, you want to remove the entire row?
If that is the case, we can use transpose tool and do the filtering.

[newbie22]

Hi Qiu,

Thank you :)

There are some null values in Att03, Att04 as well.

Sorry i missed the actual .csv file. Ive attached it for you here.

I hope that helps

Many thanks!

Meena

[danilang]

hi @newbie22 

Add a RecordID.  Transpose all the attx columns with RecordID as a key.  Summarize, counting the null values in each of the rows.  Keep only the rows with no null values   

All your rows have at least 1 null value, and your initial problem statement said "I need to remove all [null] value rows from all the columns." so at the end of the process you have no data left!  I think you need to rethink your strategy.

In your initial workflow, your filter was only on Att01 and Att03.  Are there specific columns that you need check as oppose to all of them?  If that's the case, only select those columns in the Transpose tool keeping the RecordID Key.    That way you count the nulls in any number or combination of columns

Dan

[Qiu]

@newbie22 
Thank you for the data.

As @danilang  mentioned, can you clarify the cretiria here.
If one row contains more than 1 null cell, then remove or
all columns in one row are null then remove?

[newbie22]

Hi @danilang and @Qiu,

I realised, I was using the Summarize for only understanding the output. It was not supposed to be part of the analysis at all.

I have done some more changes and attached the input csv and output as well.

My query is: I have been able to remove null[] values from Att18[], I need to remove the same null parameters from Att03 and Att04, to make the column 100%OK.

Can I use multiple IsEmpty() function, or is there another way/function to remove null[] values from all rows in each column?

I hope I have been able to explain this better

Meena

[danilang]

Hi @newbie22 

What do you mean by "remove null values"?   There is no way to remove just a single value, null or not, from a dataset.  Your options are to remove the entire row or remove the entire column.

In your workflow, you use an Imputation tool to replace the missing value with averages for columns Att10 and Att12.  You also use a filter at the end to remove all the rows where Att18 is null().  if you want to apply this filter to Att03 and Att04 to filter out the rows where any of the values are null, use a custom filter with this expression

If you want to only filter out rows where the values in all three columns are null, change the expression  

Hope this gets you closer to a solution

Dan