Need help with job design - month total logic

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

Hi
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.
tFileInputDelimited-mian--tMap_1--main--tJavaRow--main--tMysqlOutput
--runIf--tFileInputDelimited---main--tAggregateRow--tMap_2---tMysqlOutput
on tJavaRow:
output_row.Item=input_row.Item;
output_row.Month =input_row.Month;
output_row.Price=input_row.Price;
if(input_row.id==1&&input_row.Month.equals("Jan")){
globalMap.put("isBeginQuarter", true);
context.quarter="Q1";
}else if(input_row.id==1&&input_row.Month.equals("April")){
globalMap.put("isBeginQuarter", true);
context.quarter="Q2";
}else if(input_row.id==1&&input_row.Month.equals("July")){
globalMap.put("isBeginQuarter", true);
context.quarter="Q3";
}else if(input_row.id==1&&input_row.Month.equals("October")){
globalMap.put("isBeginQuarter", true);
context.quarter="Q4";
}else {
globalMap.put("isBeginQuarter", false);
}

Set the conditidion of runIf link as:
(Boolean)globalMap.get("isBeginQuarter")
tAggregateRow: populate the price group by Item column.
tMap_2: add a new column called Month, and set its value as context.quarter.
Shong
----------------------------------------------------------
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.