One Star

[resolved] Using Current date as a filter

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
1 ACCEPTED SOLUTION

Accepted Solutions
Community Manager

Re: [resolved] Using Current date as a filter

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
5 REPLIES
Community Manager

Re: [resolved] Using Current date as a filter

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
Employee

Re: [resolved] Using Current date as a filter

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

Re: [resolved] Using Current date as a filter

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
Community Manager

Re: [resolved] Using Current date as a filter

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] Using Current date as a filter

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

You inspire Us, Sir Shong! Smiley Happy