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: F1F2F3F4George42324212/03/2012 1.86m43235212/05/2020 but on some of my inputs, for whatever reason, the data is preceded by an empty column, such as this F1F2F3F4 Mike42342515/05/2013 1.73m52562330/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

How to shift data one column to the left if that entire column is null?

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

Workflow

Accepted answers

DavidP

Have a look at the below example

remove null rows with transpose crosstab.yxmd

All comments

DavidP

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.

Assaf_m

Check attached solution

Let me know if it works for you

shift data.yxmd

AkisM

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

Emil_Hasanov

Thank you! it is really charming solution 🙂 and it helped me a lot!

Quick Links

Unanswered
Community Events
Groups

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703