Dynamically add multiple New columns based on the data

[SanthoshRam]

I am unable to find the solution, I have built 80% of the workflow but got stuck at this point, below is the sample data where I am unable to proceed to the desired result,

From below data,

NEWID	Field_1
1	0 THOR 10/14/2019 D 10.00 10.00 0.00 0.00 0.00
2	1234567981010 XXX/XX
3	132 03 XX - - X - 132564987 CAL: 10/27/2019 CASH: 10.00 FTCS: 10.00 X520 X GL: XXX XXXXXXXX
4	XXX#1324657897 AGY: 2XXXXXX F/AGY: 2XXXXX A/FEE: 4.00 DUE 0.00
5	CLTR: 1XXXXXX/XXX: S/FEE: 0.00 0.00
6	0 IRONMAN 10/23/2019 D 5,00.00 5,00.00 0.00 0.00 0.00
7	1234567981010 XXX/XX
8	132 03 XX - - X - 132564987 CAL: 10/27/2019 CASH: 10.00 FTCS: 10.00 X520 X GL: XXX XXXXXXXX
9	XXX#1324657897 AGY: 2XXXXXX F/AGY: 2XXXXX A/FEE: 4.00 DUE 0.00
10	CLTR: 1XXXXXX/XXX: S/FEE: 0.00 0.00
11	34.11 XX12 X XXX XXXXXXXX
12	12234.11 XX12 X XXX XXXXXXXX
13	0 THOR 10/14/2019 D 10.00 10.00 0.00 0.00 0.00
14	1234567981010 XXX/XX
15	132 03 XX - - X - 132564987 CAL: 10/27/2019 CASH: 10.00 FTCS: 10.00 X520 X GL: XXX XXXXXXXX
16	XXX#1324657897 AGY: 2XXXXXX F/AGY: 2XXXXX A/FEE: 4.00 DUE 0.00
17	CLTR: 1XXXXXX/XXX: S/FEE: 0.00 0.00
18	0 IRONMAN 10/23/2019 D 5,00.00 5,00.00 0.00 0.00 0.00
19	1234567981010 XXX/XX
20	132 03 XX - - X - 132564987 CAL: 10/27/2019 CASH: 10.00 FTCS: 10.00 X520 X GL: XXX XXXXXXXX
21	XXX#1324657897 AGY: 2XXXXXX F/AGY: 2XXXXX A/FEE: 4.00 DUE 0.00
22	CLTR: 1XXXXXX/XXX: S/FEE: 0.00 0.00
23	34.11 XX12 X XXX XXXXXXXX

Desired output required

NEWID	Field_1	Name	Dt	NameRow_alldata	AcNum	Cash&FT1	Afees&FT2	FT3	FT4	FT5
1	0 THOR 10/14/2019 D 10.00 10.00 0.00 0.00 0.00	THOR	10/14/2019	D 10.00 10.00 0.00 0.00 0.00	1234567981010 XXX/XX	132 03 XX - - X - 132564987 CAL: 10/27/2019 CASH: 10.00 FTCS: 10.00 X520 X GL: XXX XXXXXXXX	XXX#1324657897 AGY: 2XXXXXX F/AGY: 2XXXXX A/FEE: 4.00 DUE 0.00	CLTR: 1XXXXXX/XXX: S/FEE: 0.00 0.00
2	1234567981010 XXX/XX
3	132 03 XX - - X - 132564987 CAL: 10/27/2019 CASH: 10.00 FTCS: 10.00 X520 X GL: XXX XXXXXXXX
4	XXX#1324657897 AGY: 2XXXXXX F/AGY: 2XXXXX A/FEE: 4.00 DUE 0.00
5	CLTR: 1XXXXXX/XXX: S/FEE: 0.00 0.00
6	0 IRONMAN 10/23/2019 D 5,00.00 5,00.00 0.00 0.00 0.00	IRONMAN	10/23/2019	D 5,00.00 5,00.00 0.00 0.00 0.00	1234567981010 XXX/XX	132 03 XX - - X - 132564987 CAL: 10/27/2019 CASH: 10.00 FTCS: 10.00 X520 X GL: XXX XXXXXXXX	XXX#1324657897 AGY: 2XXXXXX F/AGY: 2XXXXX A/FEE: 4.00 DUE 0.00	CLTR: 1XXXXXX/XXX: S/FEE: 0.00 0.00	34.11 XX12 X XXX XXXXXXXX	12234.11 XX12 X XXX XXXXXXXX
7	1234567981010 XXX/XX
8	132 03 XX - - X - 132564987 CAL: 10/27/2019 CASH: 10.00 FTCS: 10.00 X520 X GL: XXX XXXXXXXX
9	XXX#1324657897 AGY: 2XXXXXX F/AGY: 2XXXXX A/FEE: 4.00 DUE 0.00
10	CLTR: 1XXXXXX/XXX: S/FEE: 0.00 0.00
11	34.11 XX12 X XXX XXXXXXXX
12	12234.11 XX12 X XXX XXXXXXXX
13	0 THOR 10/14/2019 D 10.00 10.00 0.00 0.00 0.00	THOR	10/14/2019	D 10.00 10.00 0.00 0.00 0.00	1234567981010 XXX/XX	132 03 XX - - X - 132564987 CAL: 10/27/2019 CASH: 10.00 FTCS: 10.00 X520 X GL: XXX XXXXXXXX	XXX#1324657897 AGY: 2XXXXXX F/AGY: 2XXXXX A/FEE: 4.00 DUE 0.00	CLTR: 1XXXXXX/XXX: S/FEE: 0.00 0.00
14	1234567981010 XXX/XX
15	132 03 XX - - X - 132564987 CAL: 10/27/2019 CASH: 10.00 FTCS: 10.00 X520 X GL: XXX XXXXXXXX
16	XXX#1324657897 AGY: 2XXXXXX F/AGY: 2XXXXX A/FEE: 4.00 DUE 0.00
17	CLTR: 1XXXXXX/XXX: S/FEE: 0.00 0.00
18	0 IRONMAN 10/23/2019 D 5,00.00 5,00.00 0.00 0.00 0.00	IRONMAN	10/23/2019	D 5,00.00 5,00.00 0.00 0.00 0.00	1234567981010 XXX/XX	132 03 XX - - X - 132564987 CAL: 10/27/2019 CASH: 10.00 FTCS: 10.00 X520 X GL: XXX XXXXXXXX	XXX#1324657897 AGY: 2XXXXXX F/AGY: 2XXXXX A/FEE: 4.00 DUE 0.00	CLTR: 1XXXXXX/XXX: S/FEE: 0.00 0.00	34.11 XX12 X XXX XXXXXXXX
19	1234567981010 XXX/XX
20	132 03 XX - - X - 132564987 CAL: 10/27/2019 CASH: 10.00 FTCS: 10.00 X520 X GL: XXX XXXXXXXX
21	XXX#1324657897 AGY: 2XXXXXX F/AGY: 2XXXXX A/FEE: 4.00 DUE 0.00
22	CLTR: 1XXXXXX/XXX: S/FEE: 0.00 0.00
23	34.11 XX12 X XXX XXXXXXXX

I need those FT columns to be added if there is any data below the "CLTR: 1XXXXXX/XXX: S/FEE: 0.00 0.00 " , under each name 5 rows are fixed and after 5 rows it can have data (no limit) or it may not have.

Please help and my apologies if I have not explained it correctly.

Thank you!

[echuong1]

I believe the attached workflow works for your purpose. Let me know if this is what you're looking for! 

It essentially uses conditional statements and multirow formulas to populate information. 

[danilang]

Hi @SanthoshRam 

Here's a dynamic solutions that handles any number of rows 

Start by building the superhero groups and then numbering the rows within each group.  Crosstab the rows to get the data in wide format.  Parse the Field_1 data using a Regex Parse tool to give you the Name, Dt and NameRow_Alldata columns.  Rename the remaining columns based on the values in Final Column Names.  Join to the Name rows and union in all the data.  After sorting, your output looks like this

It's not shown in this image, but FT4 and FT5 only have data where required

Dan

[TonyA]

I put this together pretty quickly. It splits out the data into rows, including an arbitrary number of extra rows, but doesn't preserve the original data. If you need that I can tweak this to keep those columns.  I have an extra text input tool with the list of column names. That can be extended manually or you can extend the workflow to add more rows with column names.

[SanthoshRam]

Thank you so much @echuong1 @danilang @TonyA for the solution.

@danilangI wanted exactly the solution what you have built and I can proceed with your solution to complete my workflow but after looking into @TonyA solution which is quick and the end result is exactly what I wanted and also I don't need to preserve the original data but right now I am into dilemma, first let me provide my sample data which is one step behind,

NEWID	Field_1
1	110/29/2019 00:35 XX CCCC XXX BNK, X.X. PAGE: 1
2	BATCH #: 123456 BATCH DT: 10/26/2019 SOURCE: XXX/XX USERID: X123456
3	0 THOR 10/14/2019 D 10.00 10.00 0.00 0.00 0.00
4	1234567981010 XXX/XX
5	132 03 XX - - X - 132564987 CAL: 10/27/2019 CASH: 10.00 FTCS: 10.00 X520 X GL: XXX XXXXXXXX
6	XXX#1324657897 AGY: 2XXXXXX F/AGY: 2XXXXX A/FEE: 4.00 DUE 0.00
7	CLTR: 1XXXXXX/XXX: S/FEE: 0.00 0.00
8	110/29/2019 00:35 XX CCCC XXX BNK, X.X. PAGE: 1
9	BATCH #: 456789 BATCH DT: 10/26/2019 SOURCE: XXX/XX USERID: X123456
10	0 IRONMAN 10/23/2019 D 5,00.00 5,00.00 0.00 0.00 0.00
11	1234567981010 XXX/XX
12	132 03 XX - - X - 132564987 CAL: 10/27/2019 CASH: 10.00 FTCS: 10.00 X520 X GL: XXX XXXXXXXX
13	XXX#1324657897 AGY: 2XXXXXX F/AGY: 2XXXXX A/FEE: 4.00 DUE 0.00
14	CLTR: 1XXXXXX/XXX: S/FEE: 0.00 0.00
15	34.11 XX12 X XXX XXXXXXXX
16	12234.11 XX12 X XXX XXXXXXXX
17	110/29/2019 00:35 XX CCCC XXX BNK, X.X. PAGE: 2
18	BATCH #: 987456 BATCH DT: 10/26/2019 SOURCE: XXX/XX USERID: X123456
19	0 THOR 10/14/2019 D 10.00 10.00 0.00 0.00 0.00
20	1234567981010 XXX/XX
21	132 03 XX - - X - 132564987 CAL: 10/27/2019 CASH: 10.00 FTCS: 10.00 X520 X GL: XXX XXXXXXXX
22	XXX#1324657897 AGY: 2XXXXXX F/AGY: 2XXXXX A/FEE: 4.00 DUE 0.00
23	CLTR: 1XXXXXX/XXX: S/FEE: 0.00 0.00
24	0 IRONMAN 10/23/2019 D 5,00.00 5,00.00 0.00 0.00 0.00
25	1234567981010 XXX/XX
26	132 03 XX - - X - 132564987 CAL: 10/27/2019 CASH: 10.00 FTCS: 10.00 X520 X GL: XXX XXXXXXXX
27	XXX#1324657897 AGY: 2XXXXXX F/AGY: 2XXXXX A/FEE: 4.00 DUE 0.00
28	CLTR: 1XXXXXX/XXX: S/FEE: 0.00 0.00
29	34.11 XX12 X XXX XXXXXXXX
30	34.11 XX12 X XXX XXXXXXXX
31	0 THOR 10/14/2019 D 10.00 10.00 0.00 0.00 0.00
32	1234567981010 XXX/XX
33	132 03 XX - - X - 132564987 CAL: 10/27/2019 CASH: 10.00 FTCS: 10.00 X520 X GL: XXX XXXXXXXX
34	XXX#1324657897 AGY: 2XXXXXX F/AGY: 2XXXXX A/FEE: 4.00 DUE 0.00
35	CLTR: 1XXXXXX/XXX: S/FEE: 0.00 0.00
36	0 IRONMAN 10/23/2019 D 5,00.00 5,00.00 0.00 0.00 0.00
37	1234567981010 XXX/XX
38	132 03 XX - - X - 132564987 CAL: 10/27/2019 CASH: 10.00 FTCS: 10.00 X520 X GL: XXX XXXXXXXX
39	XXX#1324657897 AGY: 2XXXXXX F/AGY: 2XXXXX A/FEE: 4.00 DUE 0.00
40	CLTR: 1XXXXXX/XXX: S/FEE: 0.00 0.00
41	34.11 XX12 X XXX XXXXXXXX
42	34.11 XX12 X XXX XXXXXXXX
43	34.11 XX12 X XXX XXXXXXXX
44	34.11 XX12 X XXX XXXXXXXX

Basically My end result will be like this,

Report Date	Batch Date	Batch Number	Source	User Id	Name	Dt	NameRow_alldata	AcNum	Cash&FT1	Afees&FT2
10/29/2019	10/26/2019	123456	XXX/XX	X123456	THOR	10/14/2019	D 10.00 10.00 0.00 0.00 0.00	1234567981010 XXX/XX	132 03 XX - - X - 132564987 CAL: 10/27/2019 CASH: 10.00 FTCS: 10.00 X520 X GL: XXX XXXXXXXX	XXX#1324657897 AGY: 2XXXXXX F/AGY: 2XXXXX A/FEE: 4.00 DUE 0.00
10/29/2019	10/26/2019	456789	XXX/XX	X123456	IRONMAN	10/23/2019	D 5,00.00 5,00.00 0.00 0.00 0.00	1234567981010 XXX/XX	132 03 XX - - X - 132564987 CAL: 10/27/2019 CASH: 10.00 FTCS: 10.00 X520 X GL: XXX XXXXXXXX	XXX#1324657897 AGY: 2XXXXXX F/AGY: 2XXXXX A/FEE: 4.00 DUE 0.00
10/29/2019	10/26/2019	987456	XXX/XX	X123456	THOR	10/14/2019	D 10.00 10.00 0.00 0.00 0.00	1234567981010 XXX/XX	132 03 XX - - X - 132564987 CAL: 10/27/2019 CASH: 10.00 FTCS: 10.00 X520 X GL: XXX XXXXXXXX	XXX#1324657897 AGY: 2XXXXXX F/AGY: 2XXXXX A/FEE: 4.00 DUE 0.00
10/29/2019	10/26/2019	987456	XXX/XX	X123456	IRONMAN	10/23/2019	D 5,00.00 5,00.00 0.00 0.00 0.00	1234567981010 XXX/XX	132 03 XX - - X - 132564987 CAL: 10/27/2019 CASH: 10.00 FTCS: 10.00 X520 X GL: XXX XXXXXXXX	XXX#1324657897 AGY: 2XXXXXX F/AGY: 2XXXXX A/FEE: 4.00 DUE 0.00
10/29/2019	10/26/2019	987456	XXX/XX	X123456	IRONMAN	10/23/2019	D 5,00.00 5,00.00 0.00 0.00 0.00	1234567981010 XXX/XX	132 03 XX - - X - 132564987 CAL: 10/27/2019 CASH: 10.00 FTCS: 10.00 X520 X GL: XXX XXXXXXXX	XXX#1324657897 AGY: 2XXXXXX F/AGY: 2XXXXX A/FEE: 4.00 DUE 0.00

Please suggest how to proceed with this, I know I can take danilang solution by joining the report header and batch header. @TonyA do you have any suggestion or is it possible to tweak your workflow?

Thank you!

[TonyA · Accepted solution]

We could do this by adding another RowID for Batches and holding the batch columns as keys when we crosstab, I have some questions about the results. It appears that the new batch starts with one row for the Report data followed by a row that starts with the word BATCH (so we would increment the batch counter if [Row+1:Field_1] starts with "BATCH #:"). The records within the batch (first row starts with "0 ") appear to end with a row that starts with "CLTR:". The CLTR row and any row after that are not included in the output until we see the start of a new Batch or a new record. Is that correct? Also for batch 987456, why does the IRONMAN record appear twice?

I put something together assuming that we are keeping all the rows. If you wanted to drop the CLTR and addtional rows, I would tag them for deletion and filter them out before doing the crosstab. Managing the batch fields does make this more complicated. If you need to add more complex requirements, I would look at this solution and the one @danilang provided and decide which is better suited to your situation.

[TonyA]

I just looked at the data a little more closely. My question about repeating records should be why THOR doesn't appear twice under 987456.

[SanthoshRam]

@TonyA

The Name THOR can be multiple times under each batch# but the accnum will differ which is below the name row. The data which I have provided might not be the exact as original data since I have copy pasted the first 2 transaction names THOR and IRONMAN by tweaking few other information but the pattern of the data is exactly the same.

The new workflow which you have created looks great, that's exactly what I wanted but there is one more challenge which I am facing,

I have made few changes to the workflow am attaching the workflow for your reference.

The problem is the dynamically created columns till FT1 to FT3 is fixed so I can take out the information what I need by TextToColumn tool but rest FT column are not fixed it may or may not have FT4,FT5... and so on.

I just need first 2 strings from all the other dynamically created FT's starting from FT4. 

Ex. 34.11 XX12 X XXX XXXXXXXX -  I need 34.11 & XX12 as FTC4, FTC4a columns respectively for all FT's

How can I do this. Please let me know if you have any question.

[TonyA · Accepted solution]

Added/changed sections in yellow. Fixed a problem with the ReportDate column formula. And I added a section to create columns from the FTx columns.

Please mark any of the responses that helped you solve this as solutions. Also, I'd suggest if you have more questions about the workflow that you create a new message thread so others will be more likely to respond to it.

[SanthoshRam]

Awesome! that worked great.

Thank you so much for your time and help.