One Star

Need help with job design - month total logic

Hi All,
I am trying to create a talend job that has items in an Excel file with their prices for the next 3 months (including current month):
April file example:
Item | Month | Price
Gold | APR | 10
Gold | MAY | 11
Gold | JUN | 9
This file is created every month. I can currently read this file and store it in the database. However, for months that are the beginning of a quarter (Jan, April, July, October) I want to add another row to the database in the following
format given the example above:
Item | Month | Price
Gold | Q1 | 30
So basically if the monthly file I am reading is one of the 4 initial months of a quarter, I want to add another row to my table in the db that totals up the 3 months of the quarter.
Since a file is released monthly, I only have this situation 4 times a year when the months in the file correspond to a full quarter. Since the January file includes Jan, Feb, Mar I can figure out Q1. The february file would include Feb, March, Apr - I cannot calculate Q1 or Q2 from this since it has one or more months from both quarters.
Is there a way for talend to figure out if all months for a quarter is present in the data and if it is, generate the additional rows for the Q1-4 totals? i.e. I want the talend job to figure out if all the months for a quarter are present and then generate the additional row with the quarter total.
Thanks so much.
Community Manager

Re: Need help with job design - month total logic

You can read and check the first line of each file on tJava, if the months that are the beginning of a quarter (Jan, April, July, October), fires another subjob with runIf link to calculate the Query value. For example, the job design is as below, define a context variable called quarter to store the quarter name.
on tJavaRow:
output_row.Month =input_row.Month;
globalMap.put("isBeginQuarter", true);
}else if("April")){
globalMap.put("isBeginQuarter", true);
}else if("July")){
globalMap.put("isBeginQuarter", true);
}else if("October")){
globalMap.put("isBeginQuarter", true);
}else {
globalMap.put("isBeginQuarter", false);

Set the conditidion of runIf link as:
tAggregateRow: populate the price group by Item column.
tMap_2: add a new column called Month, and set its value as context.quarter.
Talend | Data Agility for Modern Business
Seven Stars

Re: Need help with job design - month total logic

I would consider calculating the quarter for each row and the using tAggregateRow to sum the Price, grouping by the quarter and pass the result to tHashOutput. Then use a RunIf trigger if there is only one row send to tHashOutput to read back the data from tHashInput and send it to your database.