How to duplicate rows and concatenate it to a table?

[kevinkwong]

I have the following text input row:

Label

A

B

C

I want to duplicate three times

Label

A

B

C

A

B

C

A

B

C

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.

[lmorrell]

Hi @kevinkwong 

Solution is attached. 

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!

[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.

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. 

Hope this helps! 

[kevinkwong]

hi @lmorrell 

It seems work but i dont know why the order is changed.

Let's go to the real dataset

After running the the workflow, I dont know why it is sorted and the null value appear in the top.

I hope it could follow the original label. How can I solve this?

[lmorrell · Accepted solution]

Hi @kevinkwong 

Updated solution is attached. 

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!

[kevinkwong]

@lmorrell  thank you so much. The flow perfectly done what I want.

Your kindly help is much appreciated!