Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

stop reading or discard any records read after the first null value encountered in field

PabloDanielovich
7 - Meteor

I have an odd situation, although I'm sure it is common.   The data file I'm pulling in (excel file) has a bunch of totals at the bottom(after some empty rows), so I want to read all the rows up to the first null value encountered in the ID field (first field). 
any ideas?

Is there any way to set the record limit dynamically or something like that? 

9 REPLIES 9
MarqueeCrew
20 - Arcturus
20 - Arcturus

I would consider using a multirow formula:

  • Create New Field = [Keep]
    • Type = Bool
    • # of rows = 1
    • Values for rows that don't exist = 0 or Empty

 

IIF([Row-1:Keep]&&!ISNULL([Data]),'True','False')

This will make all rows up to (before) the first NULL() row TRUE and all subsequent rows FALSE.  

note:  Data is the field that you're checking....

 

Now you can filter on Keep = True.

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
rohanonline
10 - Fireball

 

You may try the attached flow. I have used a text input for sample data - you may need to replace it with the excel file.

Dynamic Select.JPG

PabloDanielovich
7 - Meteor

I really like the idea behind this logic because it seems fairly simple,  but for it's not working right for me yet.
It looks like Keep is getting forced to false.  Is that because at the very beginning there is no Row-1 value?  not sure how to get around that.

AlterixReadToFirstNull.png

MarqueeCrew
20 - Arcturus
20 - Arcturus
  1. did you define Keep as Bool?
  2. in your filter you should be able to use a basic filter where Keep Is True.

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
PabloDanielovich
7 - Meteor

Rohanonline,
While it's a bit more involved of a solution (I'm trying to keep things simple),  I got your solution to work, so thanks for the help

 

PabloDanielovich
7 - Meteor

Mark,
still no workee.  I love the simplicity of it, but something is not right with the formula or workflow.  here's my test file and workflow

rohanonline
10 - Fireball

@PabloDanielovich I think your workflow had the issue of the initial value. Check the attached.

PabloDanielovich
7 - Meteor

Rohan,
Fantastic! 
Thank you so much.  I'm a total noob when it comes to Alteryx but yes, this is a great solution. 
Perhaps one day I will have a deep enough understanding to help others, but wow, this works great.  Mark, thank you as well.  

sujana117
6 - Meteoroid

This is great. I'm looking at trying to do the same with columns. I want to read all the headers until the first null value is encountered in the field. I'm assuming I would need to use a multi field formula. How would the expression change? Thanks!

Labels