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

Identify first non-null value in a sheet

gwiz
8 - Asteroid

Hi community,

I have multiple excel files that I am reading in. Per each excel file the first non-null field is the document title (what I want) but it is not always in A1, hence it is the first non-null field. All i would like to do is keep this document title and I don't need anything else. As previously alluded to it is not always in A1, it could be in B2 or F3, etc. so I am hoping for alteryx to be able to identify the first non-null field. Is this possible?

 

Thanks,

4 REPLIES 4
cmcclellan
13 - Pulsar

Use a Filter tool and then Sample the first record and you'll have it :) 

afv2688
16 - Nebula
16 - Nebula

Something like this may work,

 

cheers

danilang
19 - Altair
19 - Altair

Hi @gwiz 

 

The general way to solve a problem like this to transpose all the values into a single pair of columns and then find the 1st one.  Your main missing component is that you need to define what you mean by "1st".  Do you search in column order, row order or by applying a formula to determine the distance of every cell from cell A1.  In the following table, which is the first non-null value? 

 

 ABCD
1  Value1Casd
2 Value2Basdasd
3Value3Aasdasdasd
4asdasdasdasd

 

Is it Value3A(1st in Column A), or Value1C(1st in row 1)?  Or maybe it's Value2B since that the's closest(depending on how you're measuring distance) to Cell 1A.

 

Once you define this, you'll be able to transpose all the cells in the sheet and sort them to find the 1st non-null.  

 

BTW:  @afv2688, your workflow is missing the input file.

 

Dan

gwiz
8 - Asteroid

Yes a transpose worked! Thank you all for the advice

Labels