Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Split output to multiple sheets based on a column value and the number of rows

luaimatari7
5 - Atom

Hello All,

 

I have been stuck on this issue for quite sometime now, so it would be really great if someone could help me with this.

 

What I'm trying to achieve is a bit hard to explain so please have a look at the sample input and desired output below.  

 

Sample Input:

PeriodLine Description
Mayxyz
Mayxyz
Mayabc
Mayabc
Mayabc
Mayyyy
Mayyyy
Junexyz
Junexyz
Juneabc
Junebbb
Junexxx

 

What I'm trying to do is to split this into multiple sheets in the output file based on the "period" column. I have done this using the output tool "Change file/table name" option and then I chose the period as the field. This is working fine, however I need to limit the number of rows per each sheet, for the sake of the example lets say that the number of rows allowed in each sheet is 4. The extra twist that I need to make sure is implemented is that rows with the same line description need to also be on the same sheet. Please have a look at the desired output below:

 

Output - Sheet 1:

PeriodLine Description
Mayxyz
Mayxyz
Mayyyy
Mayyyy

 

Output - Sheet 2:

PeriodLine Description
Mayabc
Mayabc
Mayabc

 

line description 'abc' is on another sheet because it has 3 rows and cant be shared with any other line description because 'xyz' & 'yyy' each have 2 rows, therefore it will exceed the limit of rows per sheet which is 4 rows.

 

Output - Sheet 3:

PeriodLine Description
Junexyz
Junexyz
Juneabc
Junebbb

 

Output - Sheet 4:

PeriodLine Description
Junexxx

 

In the case of June , it does not matter which line description gets distributed to a different sheet alone as long as its not "xyz" (except of the two xyz line items are together within the same sheet).

 

I know this is a bit tricky and I might have not explained it very well. Please let me know if you have any questions.

4 REPLIES 4
pdave87
11 - Bolide

@luaimatari7 hi, attached workflow will provide output into different sheets based on the grouping logic you require however, I was not able to limit the output (to 4 rows) for 1 specific sheet but this is the closest solution I could think at the moment. While other experts may provide exact solution, you can test run attached workflow and let me know your thoughts.

 

pdave87_0-1634295274048.png

pdave87_1-1634295345530.png

 

pdave87_2-1634295363188.png

 

pdave87
11 - Bolide

@luaimatari7 ok, this is a step ahead (and covers 99% of requirement) of my previous solution.

 

Use same workflow and limit the number of records to 4 (as highlighted below in output data config), all records below 4 will split into new sheets (with group reference) and records > 4 will split into new workbook/file with group name appearing as a reference in sheet-name (based on output config selection). I think is as far as it goes based on my knowledge. 

 

pdave87_0-1634295892902.png

 

Regards,

Pratik

 

DawnDuong
12 - Quasar
12 - Quasar

hi @luaimatari7 

This can be done via an iterative macro.

IterativeMacro15Oct2021.PNGWf.PNG

I attached here the solution. If you want the limit per tab to be dynamic, you can add user interface to enable that.
Dawn.

luaimatari7
5 - Atom

Hi @dawnduong

thank you for this. It actually worked on the sample data, however when I connected the actual data to the macro and changed the limit of rows to 20k instead of 4, its not functioning correct anymore. I think I did something wrong. Would really appreciate if you can help me here. Please see below what I changed on the macro settings.

 

1) I changed the configuration of the multirow formula to >20000+1 instead of >4+1:

luaimatari7_0-1634573132084.png

2) I changed the configuration of the filter to be testing > 20000 instead of testing >4

luaimatari7_1-1634573197308.png

 

below you can see what the multirow formula is actually doing now for the testing field:

luaimatari7_2-1634573753406.png

 

 

Really appreciate your help ! Thanks again

 

 

 

Labels