Rookie Filter Question

[ghtill]

Basically I would like to filter if there are more than one not null values in a row for certain variables. I searched the forums and could not find an answer any help or direction to resources would be greatly appreciated. 

Would like to filter all rows that have more than one value for room type variable. but have it so it could scale to 20+ room variables as Trying to create unique conditional like: !isnull(Room) and !isnull(Room3) I don't think scales well?

Current example:

ID	Room	Key	Room2	Key2	Room3	Key3
1	DWGS	2	null	null	null	null
2	AGSW	1	null	null	KLBF	null
3	null	null	OPDF	3	null	null

Expected after filter:

ID	Room	Key	Room2	Key2	Room3	Key3
2	AGSW	1	null	null	KLBF	null

[IraWatt · Accepted solution]

Hey @ghtill,

Great question. If you are ever having to apply a filter or logic to multiple columns often you want to transpose the data onto just one column:

I do that then only look at room columns and use a summarize to count the distinct room types. If it is more then 1 I join it back onto the origional data getting your results.

This solution uses the transpose tool which can be hard to understand to new users. I would recommend checking out the community Getting-Started-Learning-Path for some context on how they work: https://community.alteryx.com/t5/Learning-Paths/Getting-Started-Learning-Path/ta-p/475117#done

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com 

[ghtill]

@IraWatt Thank you again!!

[binuacs]

@ghtill Similar to @IraWatt solution but used different conditions in the filter tool