[resolved] Using Current date as a filter

[Anonymous]

We started using TOS 4.0.2 in our organization and would like some input on designing a report that is described below (in terms of components that could be used, to efficiently do the job)
Scenario: Report to be generated with data from Sales table. The report has to run every week taking CURRENT DATE as an input. Based on the date, it needs to be mapped to a quarter (example: July 1st - Sep 30th: = 3rd quarter and so on). The quarter dates are not available in tables and need to be hardcoded.
Then, the report needs to be pick up all the sales for the current quarter (based on the sale date) and display them in a report along with current Quarter name.
Source table
SaleID SaleDate Qty Region
1 2010/01/20 3500 North
2 2010/05/15 1500 East
3 2010/07/15 1500 North
4 2010/07/15 2500 South
5 2010/07/20 1000 South
Output (cased on current date as July 22nd)
Region Quarter Total_Sales
North Q3 1500
South Q3 3500

[Anonymous]

Hi
Here is an example, I create a routine to get the quarter base on SaleDate column.
in.csv:

1 2010/01/20 3500 North
2 2010/05/15 1500 East
3 2010/07/15 1500 North
4 2010/07/15 2500 South
5 2010/07/20 1000 South

MyRoutine11709

package routines;
public class MyRoutine11709 {
	static int month = 0;
	static String quarter = null;
	public static String getQuarter(java.util.Date date) {
		month = date.getMonth() + 1;
		if (month >= 1 && month <= 3) {
			quarter = "Q1";
		}
		if (month >= 4 && month <= 6) {
			quarter = "Q2";
		}
		if (month >= 7 && month <= 9) {
			quarter = "Q3";
		}
		if (month >= 10 && month <= 12) {
			quarter = "Q4";
		}
		return quarter;
	}
}

Code in tFilterRow:

input_row.Quarter.equals(MyRoutine11709.getQuarter(TalendDate.getCurrentDate()))

Result:

Starting job forum11709 at 10:26 23/07/2010.
 connecting to socket on port 3493
 connected
.------+-------+-----------.
|        tLogRow_1         |
|=-----+-------+----------=|
|region|Quarter|Total_Sales|
|=-----+-------+----------=|
|North |Q3     |1500       |
|South |Q3     |3500       |
'------+-------+-----------'
 disconnected
Job forum11709 ended at 10:26 23/07/2010. 

Best regards
Shong

[Anonymous]

Shong,
You're doing an amazing job !! I hope the talend community knows how helpful you are.
Best regards;

[Anonymous]

That's a beautiful explanation. Thank You so much for your detailed work. I implemented the solution with minor modifications and it works real well. Modified the routine to use calendar object. One more thing that I was exploring. Is it possible to move the filtering condition inside the tMap instead of tFilterRow. By filtering it within tMap, only the rows that are necessary would be fetched. Anyways, thank you so much

[Anonymous · Accepted solution]

Hi

Is it possible to move the filtering condition inside the tMap instead of tFilterRow. By filtering it within tMap, only the rows that are necessary would be fetched.

Yes, it is possible, you can add the filter condition on tMap.
filter condition:

MyRoutine11709.getQuarter(row1.SaleDate).equals(MyRoutine11709.getQuarter(TalendDate.getCurrentDate()))

Best regards
Shong

[Anonymous]

Shong,
You're doing an amazing job !! I hope the talend community knows how helpful you are.
Best regards;

You inspire Us, Sir Shong!