join and compare dates with current time stamp on tmap

[Anonymous]

Hello,

I am trying to populate sales promotion information based on market, category and start and end time stamps.

Market and Category details are there on input and lookup files, I am doing left outer join on those using tmap.

I am facing issue with start and end time stamp. The sales promotion has to be updated as per the timestamp. This will not be there on input feed. If the current time stamp is between start and end timestamp, I want to update sales promotion data on feeds

Any idea on how to handle this?

Regards,

Mahesh

[akumar2301]

if you want out yo be generated only if current time between Start_time and end_time , you should use filter expression ( a small button with arrow mark)

with expression like

TalendDate.compareDate(TalendDate.getCurrentDate(),row6.START_TIME) >= 0 &&

( row6.end_TIME == null ||  TalendDate.compareDate(TalendDate.getCurrentDate(),row6.end_TIME) < 0 )

[Anonymous]

Thanks for the details.

I tried it, but i am getting Exception in thread "main" java.lang.Error: Unresolved compilation problem:

getCurrentDate is returning timestamp with time zone, therefore I added format of timestamp which I needed to match lookup field columns, START_DATE and END_DATE

TalendDate.compareDate(TalendDate.getCurrentDate(),row6.START_DATE,"yyyy-MM-dd HH:mm:ss") > = 0

TalendDate.compareDate(TalendDate.formatDate("yyyy-MM-dd HH:mm:ss",TalendDate.getCurrentDate()),TalendDate.formatDate("yyyy-MM-dd HH:mm:ss",row6.START_DATE)) > 0

Both of them giving type conversion error

[akumar2301]

Usually start_date and end_date is date so no parsing needed here.

You can get correct timezone time , by adding/substracting hours.

But make sure your job server is in same timezone of development server. Otherwise

[Anonymous]

In my case, start and end date has time with it, as sales promotion has to be running during that time period.

You can check the screenshot attached on my previous comment.

The issue seems to be data format. The compareDate functions expects both dates as arguments. In my case I have current date which I am parsing(TalendDate.parseDate) to convert it to date, also tried to parse start_date column to date, still seeing error.

[akumar2301]

Hello 

TalendDate.getCurrentDate() is aleady a date. But it is always in Locale Timezone.

To auto parse 2019-03-17 18:00:00 as Date , in schema definition use date pattern ""yyyy-MM-dd HH:mm:ss"" and type Date.

I tried the expression , 

TalendDate.compareDate(TalendDate.getCurrentDate(),row1.newColumn) >= 0 &&
( row1.newColumn1 == null || TalendDate.compareDate(TalendDate.getCurrentDate(),row1.newColumn1) < 0 )

it works e.g; in input I have 

2019-03-17 18:00:00;2019-03-19 18:00:00
2019-03-17 18:00:00;2019-03-18 18:00:00
2019-03-17 18:00:00;2019-03-17 19:00:00
2019-03-17 18:00:00;

Current Time for me is 2019-03-19 10:54:40

with above expression , I get 1st record and last record in Output w/o any Parsing.

2019-03-17 18:00:00|2019-03-19 18:00:00
2019-03-17 18:00:00|

Let me know if it is working for you.

[Anonymous]

Abhishek,

I tried the same solution by changing the column name as per my schema, getting error - Type mismatch cannot convert from boolean to date

Attached screenshot of my workflow

[akumar2301 · Accepted solution]

I guess , the variable you created is Date type , Above expression gives true or false.

Try like this

Do not worry about expression editor Test result. 

[Anonymous]

Many Thanks Abhishek. It worked.