ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now
The Alteryx Community will be temporarily unavailable for a few hours due to scheduled maintenance starting on Thursday, April 22nd at 5pm MST. Please plan accordingly.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Filter some limited not null values from so many columns one-by-one

Saraabdi955
8 - Asteroid

Hi,

I have So many columns in a table and I want to select only 50, for example, not null values from each column automatically.
Thanks for helping me ...

danilang
17 - Castor
17 - Castor

Hi @Saraabdi955 

 

Can you post a small sample of your input and what you'd like your output to be like?

 

Dan

Saraabdi955
8 - Asteroid

Hi @danilang 

main.png is the main test dataset and filtered.png is 2 selected records of each columns.
Thank you so much

danilang
17 - Castor
17 - Castor

Hi @Saraabdi955 

 

Here are your two images side-by-side

MainMain FilteredFiltered

 

This was a fun one because what you appear to be doing is shifting the column values up to replace null values, so your output row 1 is composed of Name from input row 2 and City and Date from input row 1.  An interesting technique I've never come across before.

 

w.png

 

The workflow transposes the input data and remove the nulls.  It then builds new output row number and crosstabs to get a new data set.  The new set can have empty values in the last set of rows, so you transpose it and count the number of empty values per row and pass on the rows that have all values filled.  Use a filter to remove the rows from the new data set and you're left with

 

r.png

 

Note that you actually have enough data to make 3 complete new output rows as opposed to the 2 that you have in your sample

 

Dan

Saraabdi955
8 - Asteroid

exactly right ...

Thanks a lot Dan @danilang 

Labels