Pages: 1

Post reply

#1 2008-01-30 11:16:05

brunodenis
New member
Registered: 2008-01-30
Posts: 5

tMap Date Comparison

Tags: [comparison, date, java, tos2.2.4]

Hi there, first post from newbie.

context :
2 input tables :
input1.id                     1|01/01/2001
input1.date                 1|14/09/2006

input2.id                     1|01/01/2001|DESCRIPTION
input2.date               
input2.desc

1 output table : gests two lines
output3.id                   1|01/01/2001|DESCRIPTION
output3.date               1|14/09/2006|
output3.desc

input1.id is mapped with outpu3.id
input1.date is mapped with outpout3.date
input2.desc is mapped with output3.desc
input1.id is linked to intput2.id as well as input1.date to input2.date (unique match)

'til then, fine. BUT.

Here is my question : I would like system to lookfor (lookup) the input2 date as inferior or equal. If no row exists in input2 for input1.date, select date from input2 where input2 <= input1.date to find previous record

Thanks in advance


Uploaded Images

Last edited by brunodenis (2008-01-30 11:22:52)

Offline

#2 2008-01-30 15:41:23

amaumont
Talend team
Registered: 2006-09-20
Posts: 471

Re: tMap Date Comparison

I think this job could help you to resolve your problem.

Do not matter to errors in tMap_2, they does not appear in final code generation.

With this job I test it with these inputs:

input1:

1|01/01/2001
1|14/09/2006

input2:

1|01/01/2001|DESCRIPTION A
1|10/08/2006|DESCRIPTION B
1|10/09/2006|DESCRIPTION C
1|10/10/2006|DESCRIPTION D

And final output is:

1|01/01/2001|DESCRIPTION A
1|10/09/2006|DESCRIPTION C


Uploaded Images

Last edited by amaumont (2008-01-30 15:42:01)

Offline

#3 2008-01-30 19:11:01

brunodenis
New member
Registered: 2008-01-30
Posts: 5

Re: tMap Date Comparison

Looks quite complex but I'll check that out tomorrow.

might be simplier to parse input1.date in the lookup sql request (where YOURFIELD <= input1.date and rownum = 1)

Many thanks anyway. I'll keep posted.

Offline

#4 2008-01-31 10:34:49

brunodenis
New member
Registered: 2008-01-30
Posts: 5

Re: tMap Date Comparison

Ok, solve the problem thanks to the given tip .

Nevertheless, it has not been necessary to build such complex job. In first tMap, lookup rejects the rows where dates not found (unique match) in a file that is the exact copy of the source.
Then, in the second tMap, I have treated rejections with your tip (filter on the lookup source.date.before(lokkup.date), last match).
So it didn't need the tJavaflex and so on...

Many many thanks anyway.

Last edited by brunodenis (2008-01-31 10:36:11)

Offline

Post reply

Pages: 1

Board footer

Powered by FluxBB