Solved

Is there a way to limit the size of Microsoft Excel Spreadsheets to say 250 Rows, so that the Writer ends up spitting out multiple Spreadsheets for Work Packages that you can then assign to members of a team?


Is there a way to limit the size of Microsoft Excel Spreadsheets to say 250 Rows, so that the Writer ends up spitting out multiple Spreadsheets for Work Packages that you can then assign to members of a team?
icon

Best answer by ebygomm 29 April 2021, 15:43

View original

3 replies

Badge +10

This is fairly easy to achieve with a fanout on the writer, I'm not sure there's any native capability.

 

e.g.

Count all your rows

Assign a group depending on this number by using an expression, first 250 rows get group 0, second get group 1, third get group 2 etc.

@Evaluate(@floor(@Value(_count)/250))

Fanout on the writer using this group

Capture 

Shout if you need more detailed info on how to do this

This is fairly easy to achieve with a fanout on the writer, I'm not sure there's any native capability.

 

e.g.

Count all your rows

Assign a group depending on this number by using an expression, first 250 rows get group 0, second get group 1, third get group 2 etc.

@Evaluate(@floor(@Value(_count)/250))

Fanout on the writer using this group

Capture 

Shout if you need more detailed info on how to do this

Hi,

 

Could you elaborate a little further with this example. Unfortunately I am not vey familiar with using SQL.

  1. Do I create this using the Expression Evaluator Transformer or Attribute Creator Transformer?
  2. @Evaluate what is the function of this?
  3. In the above example @Evaluate(@floor(@Value(_count)/250)) are you grouping by the floor attribute (e.g. 1st floor, 2nd floor)?

Thanks

 

Badge +10

Hi,

 

Could you elaborate a little further with this example. Unfortunately I am not vey familiar with using SQL.

  1. Do I create this using the Expression Evaluator Transformer or Attribute Creator Transformer?
  2. @Evaluate what is the function of this?
  3. In the above example @Evaluate(@floor(@Value(_count)/250)) are you grouping by the floor attribute (e.g. 1st floor, 2nd floor)?

Thanks

 

  1. You can use either an Expression Evalutor or an AttributeCreator to create the Group attribute
  2. @Evaluate in an AttributeCreator means it performs an arithmetic expression rather than a text expression, you wouldn't need this in an expression evaluator
  3. The expression is dividing the count by 250, then @floor effectively rounds down to the nearest whole number, e.g. 5/250 = 0.02 , @floor rounds this down to 0 so the group attribute is 0. 250/250 = 1 so the group becomes 1, 300/250 = 1.4, @floor rounds down to 1 so the group attribute is 1 etc.,

Capture

Reply