Extract Blank/Null rows from each of the dimensions/column of a file and Union them

[Achint]

Hi All,

I'm relatively new to Alteryx and trying to find a way to make it efficient to QC data. 

I need to go through all the columns of a excel report, find the NULL/BLANK rows for each of the columns and consolidate (Union) them in the end.

For example:

I have 5 columns in an excel.

Column A has 2 Null values

Column B has 3 Null values

Column C has 4 Null values

Column D has 1 Null values

Column E has 6 Null values

The output should show all the 5 columns but consolidated with NULL/BLANK values from each column. As per above example, it will have total 2+3+4+1+6 = 16 rows in total consolidated.

The process needs to be dynamic so that i dont have to create a thread for each column, it should kind of loop through all column and extract the rows.

Looking forward to hearing from you

Apart from above there is another question i posted around a month back but didnt get a reply, its a very ideal scenario for all data analysts regarding time period type MAT/YTD: 

Link:  https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/MAT-Moving-annual-total-and-Ca...

[ChrisTX]

Hi @Achint Welcome to the Community

Would it be possible to post sample input and expected output?

When you said "find the NULL/BLANK rows".... did you mean "find the NULL/BLANK cells?"  or does the entire row have to be blank?

What should the output format look like?  Different columns for A,B,C,D,E or all columns combined into one column, which may require converting different data types to String.

Chris

[geraldo]

@Achint 

An workflow example

If it is the case of having BLANK after the cross tab tools replace the BLANKS with NULL with the data cleansing tool

[Achint]

Hi @ChrisTX 

Apologies if i haven't made the problem statement clear enough, here is the example:

Only the "Red" highlighted rows in Input data table should be shown as output (combined). We need to ignore the "Green" highlighted rows as we have data in all columns.

[Achint]

Hi @geraldo 

Thanks for the solution, here i see we are counting the number of missing/null values across all column, but in my case i need to see only those rows which has atleast one BLANK/NULL cell value across all columns. Hope the requirement is clear, i've pasted an excel example above.

[geraldo · Accepted solution]

@Achint 

Other example

[Achint]

@geraldo This is exactly what i need! Appreciate your quick response!

[ChrisTX]

I think this gives the correct output