Dynamic ordering of Source System Data

[Nosal25]

We have a system that stores a CLOD-like field in 40 character blocks, with a corresponding entry number indicating the strings place in the sequence.  Is there a way to set up a workflow to dynamically reconstruct these blocks into the original input? I have a minimum entry number of 1 and a maximum of 4738.  Also, is there a way to have it stop at a max entry number and set an flag to indicate there is more data?

Thank you in advance for your assistance. 

[SeanAdams · Accepted solution]

Hey @Nosal25

There is a very cute way to do this in Alteryx using the summarize tool - summarize can group by a field, and then for text fields it can concatenate.    For your second question question - yes, you can use a filter to filter out items greater than the max, and if you find anything on this output then use a formula tool to turn it into a flag.

So if you had the following data:

ClientID	Text1
1	aaa
1	bbb
2	ccc

Then you can pump it through a summarize tool, set to group by ClientID; and to concatenate Text 1, and your output will be:

ClientID	Concat_Text1
1	aaabbb
2	ccc

Happy to mock this up if you have some test data with a mocked up output that you are looking for? 

Hopefully these hits get you going in the right direction :-)

Happy Thursday

Sean

[Nosal25]

Thank you Sean.

I will test that now.  Sample data is below...

RSN	TEXT	ENT_NUM
0001	The joys of working on a syst	01
0001	em older that I am. There was	02
0001	a reason why I didn't study Cobol i	03
0001	n college. Getting 30 year old dat	04
0001	a for a Unisys mainframe.	05
0002	Next Record	01
0002	Next Record 2	02

[SeanAdams]

workflow attached with your test data.

:-) I had a good smile at your text, counting characters in a file to decode Cobol - very very glad that this is not how we write software now-days!