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, NEWIDField_110 THOR 10/14/2019 D 10.00 10.00 0.00 0.00 0.0021234567981010 XXX/XX3132 03 XX - - X - 132564987 CAL: 10/27/2019 CASH: 10.00 FTCS: 10.00 X520 X GL: XXX XXXXXXXX4XXX#1324657897 AGY: 2XXXXXX F/AGY: 2XXXXX A/FEE: 4.00 DUE 0.005CLTR: 1XXXXXX/XXX: S/FEE: 0.00 0.0060 IRONMAN 10/23/2019 D 5,00.00 5,00.00 0.00 0.00 0.0071234567981010 XXX/XX8132 03 XX - - X - 132564987 CAL: 10/27/2019 CASH: 10.00 FTCS: 10.00 X520 X GL: XXX XXXXXXXX9XXX#1324657897 AGY: 2XXXXXX F/AGY: 2XXXXX A/FEE: 4.00 DUE 0.0010CLTR: 1XXXXXX/XXX: S/FEE: 0.00 0.001134.11 XX12 X XXX XXXXXXXX1212234.11 XX12 X XXX XXXXXXXX130 THOR 10/14/2019 D 10.00 10.00 0.00 0.00 0.00141234567981010 XXX/XX15132 03 XX - - X - 132564987 CAL: 10/27/2019 CASH: 10.00 FTCS: 10.00 X520 X GL: XXX XXXXXXXX16XXX#1324657897 AGY: 2XXXXXX F/AGY: 2XXXXX A/FEE: 4.00 DUE 0.0017CLTR: 1XXXXXX/XXX: S/FEE: 0.00 0.00180 IRONMAN 10/23/2019 D 5,00.00 5,00.00 0.00 0.00 0.00191234567981010 XXX/XX20132 03 XX - - X - 132564987 CAL: 10/27/2019 CASH: 10.00 FTCS: 10.00 X520 X GL: XXX XXXXXXXX21XXX#1324657897 AGY: 2XXXXXX F/AGY: 2XXXXX A/FEE: 4.00 DUE 0.0022CLTR: 1XXXXXX/XXX: S/FEE: 0.00 0.002334.11 XX12 X XXX XXXXXXXX Desired output required NEWIDField_1NameDtNameRow_alldataAcNumCash&FT1Afees&FT2FT3FT4FT510 THOR 10/14/2019 D 10.00 10.00 0.00 0.00 0.00THOR10/14/2019D 10.00 10.00 0.00 0.00 0.001234567981010 XXX/XX132 03 XX - - X - 132564987 CAL: 10/27/2019 CASH: 10.00 FTCS: 10.00 X520 X GL: XXX XXXXXXXXXXX#1324657897 AGY: 2XXXXXX F/AGY: 2XXXXX A/FEE: 4.00 DUE 0.00CLTR: 1XXXXXX/XXX: S/FEE: 0.00 0.00 21234567981010 XXX/XX 3132 03 XX - - X - 132564987 CAL: 10/27/2019 CASH: 10.00 FTCS: 10.00 X520 X GL: XXX XXXXXXXX 4XXX#1324657897 AGY: 2XXXXXX F/AGY: 2XXXXX A/FEE: 4.00 DUE 0.00 5CLTR: 1XXXXXX/XXX: S/FEE: 0.00 0.00 60 IRONMAN 10/23/2019 D 5,00.00 5,00.00 0.00 0.00 0.00IRONMAN10/23/2019D 5,00.00 5,00.00 0.00 0.00 0.001234567981010 XXX/XX132 03 XX - - X - 132564987 CAL: 10/27/2019 CASH: 10.00 FTCS: 10.00 X520 X GL: XXX XXXXXXXXXXX#1324657897 AGY: 2XXXXXX F/AGY: 2XXXXX A/FEE: 4.00 DUE 0.00CLTR: 1XXXXXX/XXX: S/FEE: 0.00 0.0034.11 XX12 X XXX XXXXXXXX12234.11 XX12 X XXX XXXXXXXX71234567981010 XXX/XX 8132 03 XX - - X - 132564987 CAL: 10/27/2019 CASH: 10.00 FTCS: 10.00 X520 X GL: XXX XXXXXXXX 9XXX#1324657897 AGY: 2XXXXXX F/AGY: 2XXXXX A/FEE: 4.00 DUE 0.00 10CLTR: 1XXXXXX/XXX: S/FEE: 0.00 0.00 1134.11 XX12 X XXX XXXXXXXX 1212234.11 XX12 X XXX XXXXXXXX 130 THOR 10/14/2019 D 10.00 10.00 0.00 0.00 0.00THOR10/14/2019D 10.00 10.00 0.00 0.00 0.001234567981010 XXX/XX132 03 XX - - X - 132564987 CAL: 10/27/2019 CASH: 10.00 FTCS: 10.00 X520 X GL: XXX XXXXXXXXXXX#1324657897 AGY: 2XXXXXX F/AGY: 2XXXXX A/FEE: 4.00 DUE 0.00CLTR: 1XXXXXX/XXX: S/FEE: 0.00 0.00 141234567981010 XXX/XX 15132 03 XX - - X - 132564987 CAL: 10/27/2019 CASH: 10.00 FTCS: 10.00 X520 X GL: XXX XXXXXXXX 16XXX#1324657897 AGY: 2XXXXXX F/AGY: 2XXXXX A/FEE: 4.00 DUE 0.00 17CLTR: 1XXXXXX/XXX: S/FEE: 0.00 0.00 180 IRONMAN 10/23/2019 D 5,00.00 5,00.00 0.00 0.00 0.00IRONMAN10/23/2019D 5,00.00 5,00.00 0.00 0.00 0.001234567981010 XXX/XX132 03 XX - - X - 132564987 CAL: 10/27/2019 CASH: 10.00 FTCS: 10.00 X520 X GL: XXX XXXXXXXXXXX#1324657897 AGY: 2XXXXXX F/AGY: 2XXXXX A/FEE: 4.00 DUE 0.00CLTR: 1XXXXXX/XXX: S/FEE: 0.00 0.0034.11 XX12 X XXX XXXXXXXX 191234567981010 XXX/XX 20132 03 XX - - X - 132564987 CAL: 10/27/2019 CASH: 10.00 FTCS: 10.00 X520 X GL: XXX XXXXXXXX 21XXX#1324657897 AGY: 2XXXXXX F/AGY: 2XXXXX A/FEE: 4.00 DUE 0.00 22CLTR: 1XXXXXX/XXX: S/FEE: 0.00 0.00 2334.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!

Dynamically add multiple New columns based on the data

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!

Dynamic Processing

Accepted answers

TonyA

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.

BuildBatches.yxmd

TonyA

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.

BuildBatches2.yxmd

All comments

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.

New Workflow3.yxmd

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

DynamicDataColumns.yxmd

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.

BuildCols.yxmd

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

BuildBatches.yxmd

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703