0

I have multiple table inputs joined by a cartesian and a Microsoft excel writer output. The table inputs are basically writing from left to write on the Microsoft Excel Writer. I need it to skip a column so i can put a formula there.

I was told I can put a design in pentaho to act as an empty dummy then just write the formula on the excel column in my template.

Can someone help me? Thank you

EDIT : Transformation

Spreadsheet

ajburnett34
  • 27
  • 1
  • 10

1 Answers1

0

There are several ways you can achieve this:

  1. Migrate your excel formulas into Pentaho, instead of leaving a formula column on a template. Work the data within the PDI Design and just output the entire result into an Excel.

  2. In your template excel, leave the formula column in A, and simply output the cartesian product starting on B column onward.

There are other several ways but those 2 are the most simple. Things like splitting the result table for 2 separate Excel Writer steps can mess things up a bit, and also severely slow down the process depending on how many rows you output.

EDIT 1 - enter image description here

Cristian Curti
  • 1,004
  • 1
  • 7
  • 13
  • What design step who i use? I have TWO table inputs (input), join rows, and microsoft excel writer (output) – ajburnett34 Jun 15 '18 at 20:43
  • What formula is it that you have on the Excel file you are loading the data into ? There is a step "Formula" in PDI that has some of the basic Excel Formulas for use. – Cristian Curti Jun 18 '18 at 14:11
  • I need to simply subtract columns B-C and have it writethe answer on column D. I have my data starting on column A – ajburnett34 Jun 19 '18 at 13:49
  • If i could get an example i could help you better. If by subtract you mean Math B-C, then the Step Calculator is your answer. Before going to output you can use the Select Values step to rearrange the order of the columns. – Cristian Curti Jun 19 '18 at 16:06
  • Hi Cristian i have added two screen shots in my original post. In the spread sheet i neeed to subtract column b-c and the result should go under pending on column D. The numbers in column D and E need to shift over to the right one so they can be under column E and F. – ajburnett34 Jun 19 '18 at 17:59
  • I have edited my answer with an image, your flow and steps should look like that. The new column (COUNT - COMPLETED) will be created in the Calculator step, and in the select values step you will rearrange the columns. – Cristian Curti Jun 20 '18 at 18:30