Need a solution in forming a properly aligned Output

[GouthamVijay]

Hey Experts,

I am facing a problem in data formatting while using "Text to Columns" tool

Input:

After applying "Text to Columns" tool with following Configuration

I got output like below

There is a small difference with what i expected, ie...

If you see  T452909 value has been printed after Read Access, ideally it should be attached with values in "Access2" column

Expected Output should be looking like this

If you see now the value T452909 is appended next to the values( A242116, CHISGRP(G), CHIS01(S), CHIS02(S), CHIS04, T452909) in "Access2" column...

This should work as Dynamic,, the extra row can appear after Read Access or Update Access or Control Access rows but it should be attached to the proper "Access2" rows always...

Thanks in Advance.....

PS: Input file been attached..

[CharlieS]

Hmmm, I think the best course of action would be to address that line break before the Text to Columns. 

Could you share that example in text instead of an image for the Community to take a crack at?

[Matt_D]

Hi @GouthamVijay

What is the actual input? .csv, .xlsx? A screenshot doesn't help (as it cannot be copy and pasted) and to build a workflow becomes a pain as we have to build the input manually. 🙃 Can the input be provided as is without any manual changes?

First suggestion is to use a multirow formula tool and look for rows containing : that end with , and then concatenate the next row on the end of the current row. If that next row is now contained within the one above, return nothing else return the row. Should cleanse your data for the text to columns tool.

I would like to know what happens if you give 100 users Read Access, do you get many rows?

[DataNath]

Does this work for you @GouthamVijay? It basically appends the following row if either of those fields you mentioned (X Access) are missing so then it appears in the correct access2 row. You're still left with the code in Access 1 after the split to column and so I've added an extra formula tool to clean that up. Any issues let me know and I can revisit!

[Matt_D · Accepted solution]

How about this? Should capture the 100 users scenario if you get more than 1 line break.

[GouthamVijay]

Hey @Matt_D 

Sorry I forgot to attach an input file, your understanding is correct..more rows may get generated as the users get increased. 

FYI, the actual input is in a text file format...

As there was a confidential data, I have copied the output from alteryx into an excel file and the same being copied here....

Please check my original post for the Input File...(Thanks for reminding)

[GouthamVijay]

Thanks for reminding..I did attach now please check...