Alteryx Designer Desktop Discussions

Asalmeida · ‎02-25-2021

Hello,

I have been trying to work my way around this but couldn't make it happen. I just started using Alteryx and this seems to be out of my reach.

My data is in a single column and I want to make it look like the following:

-The data represented by the same letters isn't the same but refers to the same attribute.
-Some data groups have 8 rows, some have only 7.

-If Row G isn't present I would need to shift row F to Column7
-X is the last data cell for each group, and a part of it is always the same, so that can be used.

Many thanks in advance for you help.

AngelosPachis · ‎02-25-2021

Hi @Asalmeida ,

I think this workflow might do the trick. I have used the annotations to explain what happens in each step but feel free to reach out if there are any further questions.

Cheers,

Angelos

Asalmeida · ‎02-25-2021

Hi AngelosPachis,

Thank you for your response and time.

I had to change the Assign column ID multi row formula to
IF CONTAINS([row-1:Input],"X")
THEN 1
ELSE [Row-1:Column ID]+1
END IF

And the create groups multi row formula to

IF CONTAINS([row-1:Input],"X") THEN [Row-1:Group]+1
ELSE [Row-1:Group] ENDIF

Since the X is only part of the data cell. Is there anything wrong with this changes?

I also had to change the replace the change columns formula since my data set has 29 or 30 cells per group instead of 7 or 8 that I have in the sample.

Most of it look fine, but in the rows that have 29 cells with data he last one -"X" doesn't show. And some cells aren't shifting correctly but that should all be related.

Thank you

AngelosPachis · ‎02-25-2021

Hi @Asalmeida ,

Yes so a contains function will behave differently than a normal equation which would look for an exact match. That will influence the groups you are creating and inevitably how your table will be structured.

The issues you faced with the provided solution were to be expected but it was difficult to avoid them, so an exact implementation wouldn't work and it was more of a proof of concept.

If you can take away that proof of concept and work with it so it works for you then great, otherwise maybe you can share a more representative sample of your data so I can assist you further?

Thanks

Angelos

Asalmeida · ‎03-09-2021

Hi Angelos, thank you for your response.

I tried myself but I could not solve this unfortunately.

Having the original post in mind, I have attached a dataset that represents the real one much better, with 4 different groups and the correct amount of rows in each group. The Model: might have 3 or 4 rows, but it always has Material: after it. When Model: only has 3 rows, the 3rd one needs to go into the 4th column, like this:

AAAAAA	Model:	Circular	Large	Horizontal	Thin	Material:
AAAAAA	Model:	Circular	Large		Horizontal	Material:

To close all the groups we have Level: (always present) and XXXXXX (something following it). So we can rely on the Level: part of the cell to close the loop.

Let me know if I wasn't clear enough.

Thank you for your help.