Removing columns if certain rows are null
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi everyone
I have a dataset that looks like this:
Record ID | F1 | F2 | F3 | F4 |
[NULL] | [NULL] | Building 1 | Building 2 | Building 2 |
5 | 12/31/2021 | 200 | [NULL] | 654 |
8 | 12/31/2021 | 300 | [NULL] | 200 |
I'm trying to remove column F3 entirely from the dataset because F4 already has the data for Building 2, so I want the dataset to look like this:
Record ID | F1 | F2 | F3 or F4 (this name doesn't matter) |
[NULL] | [NULL] | Building 1 | Building 2 |
5 | 12/31/2021 | 200 | 654 |
8 | 12/31/2021 | 300 | 200 |
Using the Select tool would not work because the column(s) with null rows to be removed differ each time.
For the purposes of this dataset, there will always only be 3 rows with the first row being NULL, but the Record ID numbers can change each time (this time it's NULL, 5 and 8, next time it could be NULL, 9 and 15).
Thanks in advance for your help!
Solved! Go to Solution.
- Labels:
- Transformation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks for your quick responses!
Unfortunately both of your solutions don't quite work because in this scenario, you're renaming the columns manually, however, I need it to be dynamic.
For example, another input file may look like this, where I want column F2 removed:
I guess to summarize things, I'm looking for a way for Alteryx to dynamically remove any columns in which the last 2 rows are [null].
Another example, where I want to remove columns F3 and F4 but keep the rest.
I didn't want to rename the headers until after I remove the columns with null rows because what Alteryx changes the name of the second column if there are two headers that share the same name. This changes the name of the column to something different which doesn't work.
Also, the [NULL] I wrote is actually just a null value from Alteryx ([null]), and not text that says "[NULL]". My apologies that I wasn't more clear in my original post.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks! This works!
