Checking For Blank Fields on Large Table

Hi All

I was wondering if someone could please help me ?

I have a large table, that is 2m lines long and 223 columns wide. I have been asked to run this through Alteryx and apply the following logic :-

If the field contains a 0 or a blank or is null then mark it with a 0

If it contains text then mark it with a 1.

Then I have to sum each field to find out how many times there is a value in each field. (Count all the 1s)

I dont want to have to set up 223 fields with either a Case Statement or If Then Else as I'd probably be an old man with a white beard by the time I finish.

Is there another way of doing this in an automated fashion.

Any help would be gratefully received.

Many thanks

Paddy

Developer

Macros

Transformation

In Database

Machine Learning

In-Database

Accepted answers

All comments

messi007

@Paddydd,

Could you share a sample data? It will help us to give the solution 🙂

Regards

atcodedog05

Hi @Paddydd

Can you provide sample input and expected output. So that we can get a better understanding 🙂

dataguyW

I would check into the Multi-Field Formula tool in the Preparation tools. You can write the formula once and have it apply to many columns.

Paddydd

Here is a sample of the data. I've populated it with a figure but some fields are 0 and some Blank.

Does this help ?

SampleData.xlsx

atcodedog05

Hi @Paddydd

What is the expected output?

OllieClarke

Hi @Paddydd

Like the others said, if you can give us an example output, that would help. However, taking a punt, this workflow will give you a list of columns and whether those columns contain a 0 or blank as a boolean field:

0 or empty.yxmd

Paddydd

Hi AtCodeDog05

A count of every field where the cell <> 0 or <> Blank or Is Not Null

messi007

@Paddydd,

1- Below the fomula to update th value for all columns

2- Cross tab to get back to the source data

3- I don't understand the second part when you say "how many value on each field"

But what I did is I filtred the 0 (Updated value) and did the summarize on them:

Attached the workflow.

Hope this helps!

Regards

Clean_20210820.yxmd

atcodedog05

Hi @Paddydd

Refresh the page and try again might work 🙂

dataguyW

Multi-Row and Basic Sum version. This assumes you know how many columns (as stated). Because your datatypes were mixed vachar/double you just change the Multi-Column formula to All Types so you can click "Select All". When you do the Summary Tool, select the first Column, hold shift, and then select last column on to make it faster to select all columns.

MultiRowValueChecker.yxzp

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703