I have the following text input row: LabelABC I want to duplicate three times LabelABCABCABC And I have a 9x8 table. I want to concatenate the label row to the table and become a 9x9 table: 12345678LabelxxxxxxxxxxxxxxxxAxxxxxxxxxxxxxxxxBxxxxxxxxxxxxxxxxCxxxxxxxxxxxxxxxxAxxxxxxxxxxxxxxxxBxxxxxxxxxxxxxxxxCxxxxxxxxxxxxxxxxAxxxxxxxxxxxxxxxxBxxxxxxxxxxxxxxxxC How can I achieve this? Thanks a lot.

How to duplicate rows and concatenate it to a table?

I have the following text input row:

Label

I want to duplicate three times

Label

And I have a 9x8 table. I want to concatenate the label row to the table and become a 9x9 table:

1	2	3	4	5	6	7	8	Label
xx	xx	xx	xx	xx	xx	xx	xx	A
xx	xx	xx	xx	xx	xx	xx	xx	B
xx	xx	xx	xx	xx	xx	xx	xx	C
xx	xx	xx	xx	xx	xx	xx	xx	A
xx	xx	xx	xx	xx	xx	xx	xx	B
xx	xx	xx	xx	xx	xx	xx	xx	C
xx	xx	xx	xx	xx	xx	xx	xx	A
xx	xx	xx	xx	xx	xx	xx	xx	B
xx	xx	xx	xx	xx	xx	xx	xx	C

How can I achieve this? Thanks a lot.

Datasets

Accepted answers

lmorrell

Hi @kevinkwong

Updated solution is attached.

How to duplicate rows and concatenate it to a table v3.png

Now that I see the intended labels, I realised the sort order doesn't work when the labels are not in alphabetic order! By attaching a Record ID and calling the column "Label Order" we can use this column to ensure the sort order remains consistent with the original dataset.

Hope this helps!

How to duplicate rows and concatenate it to a table v3.yxmd

All comments

lmorrell

Hi @kevinkwong

Solution is attached.

How to duplicate rows and concatenate it to a table.png

I'm sure there are many ways to achieve this, but one way is by using the Generate Rows Tool to duplicate every row by 3, and then using a modulus formula on the Row ID we can sort to restore the original pattern and obtain the 1x9 Label Table. Then by joining this dataset directly to the 9x8 table based on Record ID, we can concatenate the two together.

Hope this helps!

How to duplicate rows and concatenate it to a table.yxmd

kevinkwong

hi @lmorrell

How if my label is not ABC repeatedly but ABCDEFGHI?

By using your suggested soluation, I found that the sequence is not aligned as what i wanted.

kevinkwong

hi @lmorrell

Also, I have used directory and count row to count the number of .xlsx file inisde the folder. In this case it is 4. Then I want to duplicate the label row 4 times.

How can I link up the number of 4 into the formula? The formula logo only allow one input.

Thanks!

lmorrell

Hi @kevinkwong

An updated workflow is attached.

How to duplicate rows and concatenate it to a table v2.png

a) To change the label values, I have updated the "A, B, C Dataset" to contain letters from A -> I. As long as the original Label dataset is updated, all the rows within it will be duplicated by the Generate Rows Tool. However, the sort order was not dynamic which may have resulted in an error - so by counting the number of rows in the Label dataset and using this in the modulus calculation, the sort order can become dynamic.

b) To duplicate the label by the number of excel documents - I have created a temporary datasource called "Count Records Substitute" that has the number 4 in it. By appending this to the Label dataset and then referencing it in the Generate Rows Tool, this component will become dynamic when you use the same logic in your own workflow. (Attach the output of the count records to the Append Tool)

However, one thing to note is that the 9x8 dataset should become a 9x36 dataset so that each label gets attached on the row level. Otherwise the Labels will have no data to attach to.