How to shift data one column to the left if that entire column is 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
I am inputting multiple excels at once, auto-config by position (can't auto-config by name due to irregularities in the files). While the inputs are supposedly of somewhat similar format, they're not really, so I have to standardize them. Some of my inputs look like this:
F1 | F2 | F3 | F4 |
George | 423242 | 12/03/2012 | |
1.86m | 432352 | 12/05/2020 | |
but on some of my inputs, for whatever reason, the data is preceded by an empty column, such as this
F1 | F2 | F3 | F4 |
Mike | 423425 | 15/05/2013 | |
1.73m | 525623 | 30/08/2017 | |
What kind of tool/formula/rule can I use to make sure the inputs where the entire 1st column is empty, all the other data is shifted one column to the left, so that I can unionize it with the other data and process it as 1 stream? Note that the data should only be shifted if the entirety of column F1 is empty. Not wherever a single cell of F1 is empty
Solved! Go to Solution.
- Labels:
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
In Alteryx Designer 2020.2, the Data Cleanse Tool has been updated with options to remove null columns or rows.
You can also do this by transposing the data set, filter [Value] with an !IsNull() or !IsEmpty() statement and CrossTab back to the original format. If you do this you should add a RecordID first and mark that as a Key field in the Transpose and also Group by in the CrossTab.
- 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
Hi @DavidP , unfortunately I can't upgrade to that version.
Could you attach a sample workflow using the method you describe? The transpose tool is something I still need to work on a bit
- 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
Thank you! it is really charming solution 🙂 and it helped me a lot!
