tMap Expression Builder Conditional logic java lang Error

[Anonymous]

Hello Team,
Currently I'm working on a way to compare the data coming from two different excel sheets, I'm using two tFileInputExcel then doing some data transformations using tJavaRows (parsing dates, and some strings containing numbers to integer). After doing this Im receiving the two inputs into a tMap in which I need to compare two rows in order to divide the information. 
In order to compare the row I need from the input1 and the one from the input2, I have a conditional as follows (An example of what I'm doing):
                   row10.field4>row12.field4?row10.field3:row12.field3 
When running the program I am getting the error "Caused by: java.lang.Error: java.lang.Error: Unresolved compilation problems: row12 cannot be resolved to a variable"... it does not recognize the row12 elements..
Is there a way I could use the values from the second input in the tMap? Making long story short, in both excel sheets I have a date from which I extract the hour (myDate.getHours()+":"+myDate.getMinutes()+":"+myDate.getSeconds()) and then from this I get, on a field apart, the minutes (in both tJavaRows), what I need to do is to put in an excel, as incorrect, the data in which the hour from the input1 exceeds the hour from the input2 in 15 minutes or more than 15 minutes, this and some more validations Im doing in the tMap. Originally in the tMap it rejects every row containing any different information from the input2, the problem is that with the hour, even 1 minute different is taken as incident but the system should let pass if the hour has a difference in minutes of less than 15 minutes

if input1Hour > input2Hour then I verify if input1Hour-input2Hour < = 15 if thats true I let the date from the input1 as the date from the input2 so the inner join will let it go through as OK . if the input1Hour-input2Hour > 15 then I live the date from the input1 exactly as it is so the inner join will reject it as it is supposed to.

Please check the images so you can understand the way Im doing this.
 
Regards,
hbotalend.

[Anonymous]

not sure why this is not working for you.  one quick solution would be to do the comparision outside the tMap like in a tJavaRow for example.
I find the tJavaRow has more flexibility.  tMap is good but it has its drawbacks such as "die on error" or retry, etc.

[Anonymous]

The tMap is completely the wrong component for this I am afraid. You are also trying to replace a join from the main flow (row10) to the lookup (row12) with a join from the lookup (row12) to the lookup (row12) with the conditional logic. This would never work. tMap components have a concept of a main flow and a/several lookups. For every row from the main, the lookups are compared. But the process is driven by the main.
In your example, are there any natural joins between the excel files? Or are you wanting to carry out a cartesian join in order to compare every row? If you want the cartesian join you could (and this is not likely to be the most efficient solution to your problem) use the tMap without a join between the two inputs. Then merge the two file's columns into one output. So the data would look like below...
Excel 1 (main)
1,a 
2,b
3,c
Excel 2 (lookup)
4,d
5,e
6,f
Output
1,a,4,d
1,a,5,e
1,a,6,f
2,b,4,d
2,b,5,e
2,b,6,f
3,c,4,d
3,c,5,e
3,c,6,f
You can then carry out your comparison for every combination.
As I said, this will not be the most efficient way of solving your problem at all, but it will demonstrate how you can use a tMap to maybe solve your issue. To get a better idea of what you need to achieve, maybe you could give us a data example like I have done above.

[Anonymous]

Thanks for your replies,
@ rhall_2.0
What happens is that originally I needed to extract all the discrepancies from excel1 with excel2  (without taking into consideration the 15 minutes difference) it worked perfect since the inner join is separating all the diferent rows and the rows with the exact same information in both files and I'm sending the outputs into two separeted files ("difference.xls" and "sameness.xls")
These times are the departure and arrival of some products and we have two systems to control this information, I receive an excel from each and I have to make sure the data in both is correct comparing the names of the products, id, date, time, etc, so I did this with the Inner Join -> catch lookup inner join reject (true)
but now I need to stablish that a 15 minutes difference among both files must be taken as it was the same time in both files, thats why Im trying to use the conditional in the Expression Builder.
Taking the minutes from the input1 Date row and the one from the input2 Date row, if the difference is less than the 15 minutes, for the inner join to let the row go through, I'm trying to exchange the the Date from the input1 with the Date from the input2 so the inner join will find that as equal and let it go... Isn't this possible?
@ edgars4Talend
I don't see how to make this verification previously in a tJavaRow since I wouldn't be able to pass the Date row from the tjavaRow2 to the tJavaRow1

Regards.

[Anonymous]

Ah, I see. How about keeping the inner join on the products, etc, and then dealing with the time difference in a tMap variable?
If you create a tMap variable of type boolean and use the routines.TalendDate.diffDate(date1, date2, dateType) method to calculate the difference between the two dates (down to second or minute, whatever you want). Then use some boolean logic to establish the true or false criteria. For example....

routines.TalendDate.diffDate(date1, date2, "mm")>15

....would return true if the difference between the two dates is greater than 15 minutes and false for the same as 15  minutes or less. You can then set the filter on the output tables to look at this computed variable and pass the corresponding data to these outputs accordingly.

[_AnonymousUser]

@rhall_2.0 I've been working on it during my free time but apparently I can not make it work properly. I added the tMap variable for the departures and Arrival comparison and the verification on the output:

routines.TalendDate.diffDate(row2.DepTime,row8.DepTime,"mm")<15  ----> var1
routines.TalendDate.diffDate(row2.ArrTime,row8.ArrTime,"mm")<15          --------> var2

for the Output validation in the expression filter:
Var.var1==true&&Var.var2==true  ---> As the correct values ( differences less than 15 minutes)
Var.var1==false||Var.var2==false ---> for the discrepancies
But even if the difference is of one minute, it is taking it as a difference instead of letting it go through.
Does the diffDate needs an specifc format date? my DepTime and ArrTime are Date type, I tried formatting those like:
routines.TalendDate.diffDate((TalendDate.formatDate("yyyy-MM-dd HH:mm:ss",row2.DepTime)),(TalendDate.formatDate("yyyy-MM-dd HH:mm:ss",row8.DepTime)),"mm")<15   
but that does not work since is changing the type from Date to String and the diffDate requires paremeter Date,Date,String...

(Sorry the site is not letting me login)

[Anonymous]

You have to remember that "diffDate" works by subtracting date2 from date1 (date1-date2). The unit you specify ("mm" in this case) is just how it represents the difference. So if date1 is 2015/06/20 15:00:00 and date2 is 2015/06/19 14:59:00, that would be 1441 minutes as it is a day and 1 minute difference. 
The best way to debug what is actually happening is to put the logic into a routine. Create a new routine and add a method like below....

public static boolean dateDiffWithinBounds(String unit, Date date1, Date date2, int bound){
     boolean returnVal = routines.TalendDate.diffDate(date1,date2,unit)<bound;
     System.out.println("Date1 = " + TalendDate.formatDate("yyyy-MM-dd HH:mm:ss",date1) + " and Date2 = " + 
     TalendDate.formatDate("yyyy-MM-dd HH:mm:ss",date2) + ". Result = "+returnVal);
     return returnVal;
}

 
If you use this method in your tMap it will print out the date1 and date2 values with the result for each comparison. From that you should be able figure out why it is not working.
Note: This was written directly in this post, so may not be entirely bullet proof. It should give an idea though.

[_AnonymousUser]

What I did was to place an output with the result of the TalendDate.diffDate(Date1,Date2,"mm") to verify why is not working, then I sent the following dates:
Date1=Sun Jun 21 15:45:00 CST 2015
Date2=Sun Jun 21 15:30:00 CST 2015
the result for this comparisson was 23915370
Date1=Sun Jun 21 15:45:00 CST 2015
Date2=Sun Jun 21 15:40:00 CST 2015
is 23915380
Date1=Sun Jun 21 15:45:00 CST 2015
Date2=Sun Jun 21 15:50:00 CST 2015
is 23915390
Which I guess is the time since epoch but don't get why is showing the result like that

[Anonymous]

Are you manually setting those dates (date1 and date2)? If so, something is going wrong there. I have tested the example you gave with the following dates.....
Date1=Sun Jun 21 15:45:00 CST 2015
Date2=Sun Jun 21 15:30:00 CST 2015
I ended up with a time difference of 15 mins. 
The layout of my example job and the output is below.....

The configuration of the tMap is below....


I suspect that you are converting a String to a date and the conversion is going wrong. Can you take a few screen shots to show us what you are doing exactly?

[_AnonymousUser]

In fact kind of manually, becasuse the problem is that in one ofthe inputs the date is divided like column1 day-month-year and the column2 is hour:minutes:seconds and that hour is taken in the format of "Sun Dec 31 15:45:00 CST 1899"
One more proble is that one of the excels does not keep the departure day, like for example the departure was on the second of june and arrival the third, it only keeps departure and arrival time with the arrival date.
so I had to make conversions to "fix it"
Does the dates for the diffDate have to have the same format and year and everything? or does it especifically extract the hour and compares both? something like 
Sun Dec 31 15:45:00 CST 1899
Sun Jun 21 15:30:00 CST 2015
and only compares 15:45:00 with 15:30:00??
I'll try to add some screenshont but from were I am now the login its like blocked.