Move entire row to the right

Hi,

I'm dealing with data that looks like this:

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.

Rows.xlsx

Workflow

Preparation

Accepted answers

danrh

This was a fun one

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.

solution.yxmd

Peter4

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

solution-1.yxzp

All comments

CharlieS

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.

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

20181015-RowShift.yxmd

danrh

This was a fun one

solution.yxmd

ponraj

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.

Workflow Results Results

Move entire row to the right.yxmd

Peter4

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

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.

Rows.xlsx

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703