topic Re: How to compare a date and a periode? in Talend Studio

How to compare a date and a periode?

Anonymous — Sat, 04 Nov 2017 23:05:05 GMT

Hello everyone,

I want to type a person according to a periode, so I need to compare between a date and a periode.

For exemple:

I have two tables

Table A

Table B

I want to have:

two first lines of Table A get type A

two last lines of Table A get type B

here date_debut means start date, date_fin means end date

So I use tmap, at first time I try like that

Match model is unique match

But the result is

1|03-03-2017|||
1|04-03-2017|||
1|05-03-2017|05-03-2017||B
1|06-03-2017|05-03-2017||B

it dosent work on two first line

Then I try to put the condition row1.id_pers==row2.id_pers in the expression zone, the match model becomes All rows

Now it works, I get a correct result

1|03-03-2017|03-03-2017|04-03-2017|A
1|04-03-2017|03-03-2017|04-03-2017|A
1|05-03-2017|05-03-2017||B
1|06-03-2017|05-03-2017||B

But the problem is the performance is not good when I use the second way on the real data, it spends so much time.

So do someone know why the first way doesn't work?

Thank you in advance.

JulienF

Re: How to compare a date and a periode?

ThWabi — Mon, 06 Nov 2017 14:30:17 GMT

Hello JulienF,

"the first way" joins your tables A and B on "id_pers" and with the match model "unique match" (which is equal to "last match") only the second row of table B is in the join result / lookup data. Then tMap evaluates your date-compare-expression. For the first two (input) rows of table A, their date does not match the date range of the lookup, so they do not have a "type".

My suggestion is: Join the two tables only on the id_pers field. Use "All matches" as the match policy. Do not use this expression area, instead move the date comparison to the "Var" table.

Best regards,

Thomas

Re: How to compare a date and a periode?

Anonymous — Tue, 07 Nov 2017 09:42:04 GMT

Hi ThWabi,

Thanks for your explication and your suggestion.

It's very clear.

But I didn't really understand that you said "Do not use this expression area, instead move the date comparison to the "Var" table.".

How to move the comparison to the Var Table? Could you give me more detail about this point, please?

Best regard,

Julien FAN

Re: How to compare a date and a periode?

Anonymous — Tue, 07 Nov 2017 13:01:34 GMT

Hi Julien,

I think a job like this one can do what you want, it uses "Var table" like ThWabi suggested.

Best regards.

TD (bon courage)

Re: How to compare a date and a periode?

Anonymous — Tue, 07 Nov 2017 14:14:44 GMT

Thank you tdz, it works very well.