Alteryx Designer Discussions

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

Move entire row to the right

Peter4
8 - Asteroid

Hi,

 

I'm dealing with data that looks like this:

 

Excel1.PNG

 

As you can see, the columns are shifted to the left, but I'd like to have all columns with Owner Name, Owner ID, Shop ID etc underneath each other. It's fairly random as to how far they're shifted to the left and almost every row is different from the one above. I've got files for several years and it happens in every file, but not necessarily in the same row. I'm inputting all files at the same time with one input tool so they get unioned together, but I've managed to seperate the misaligned rows. I was wondering if there was a way to align them properly in Alteryx.

6 REPLIES 6
CharlieS
17 - Castor
17 - Castor

I suggest making the change to the system that produces this document. It seems that fields with repeated values are left blank. If these fields were populated, this will solve your alignment problem and allow each row to be evaluated on their own. Using your example file, the yellow fields are values that were missing and populating these corrected the alignment problem. 

 

PopulateFields.png

 

 

 

 

 

 

 

 

 

 

 

 EDIT: That felt like a lazy response, so I've attached a solution that solves this in Alteryx. 

danrh
13 - Pulsar

This was a fun one 🙂

image.png

The key is to Transpose, reverse the order of the fields, and Cross Tab. This essentially "right-aligns" all of your fields. I tested it out with both sheets in your sample data and it seems to be working. I also took the liberty of playing the the naming portion to get it into a more usable format.

ponraj
13 - Pulsar

Here is the sample workflow for your case. Little complex though. It works perfectly with the two sample table you shared. Hope this is helpful. 

 

WorkflowWorkflowResultsResults

Peter4
8 - Asteroid

Wow, thanks for the swift response everyone! I will go through your solutions tomorrow with the real data and will let you know which approach I took!

Peter4
8 - Asteroid

Hi danrh,

 

I see what you're doing there, but it doesn't quite work for me. I think it's because of the IsNotEmpty filter. I do have columns which are empty, but the dataset I attached yesterday didn't reflect that. I have attached a more accurate version.

 

Thank you for renaming the fields, that's very helpful and much more elegant the the solution I came up with.

Peter4
8 - Asteroid

Oh I think I've got it!

 

Since every row is different, I have to split them out into different branches and replace Nulls with a value depending on how far the columns have been shifted to the left. Now I just have to turn the repeating steps into a macro and figure out how to solve my problem if I've got several years in my input data rather than just one as I won't be able to use the sample tool. Thanks so much for your help, I wouldn't have been able to solve this without your input!

Rows solution.PNG

Labels