topic Re: [resolved] Using Current date as a filter in Talend Studio

[resolved] Using Current date as a filter

Anonymous — Sat, 16 Nov 2024 13:20:31 GMT

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

Re: [resolved] Using Current date as a filter

Anonymous — Fri, 23 Jul 2010 03:39:32 GMT

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

Re: [resolved] Using Current date as a filter

Anonymous — Fri, 23 Jul 2010 03:57:26 GMT

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

Re: [resolved] Using Current date as a filter

Anonymous — Sat, 24 Jul 2010 21:48:34 GMT

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

Re: [resolved] Using Current date as a filter

Anonymous — Sun, 25 Jul 2010 05:28:08 GMT

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

Re: [resolved] Using Current date as a filter

Anonymous — Mon, 26 Jul 2010 07:07:14 GMT

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

You inspire Us, Sir Shong!