Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

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

AkisM
9 - Comet

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:

 

F1F2F3F4
George42324212/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

5 REPLIES 5
DavidP
17 - Castor
17 - Castor

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
11 - Bolide

Check attached solution 

 

Let me know if it works for you

 

Assaf_m_0-1596008372758.png

 

AkisM
9 - Comet

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

DavidP
17 - Castor
17 - Castor

Have a look at the below example

 

DavidP_0-1596013985398.png

 

Emil_Hasanov
7 - Meteor

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

Labels