Four Stars

How to compare a date and a periode?

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

2.JPG

Table B

3.JPG

 

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

 

5.JPG

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

6.JPG

 

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Six Stars

Re: How to compare a date and a periode?

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

 

Five Stars tdz
Five Stars

Re: How to compare a date and a periode?

PersonneType.jpg

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)

 

4 REPLIES
Six Stars

Re: How to compare a date and a periode?

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

 

Four Stars

Re: How to compare a date and a periode?

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

Five Stars tdz
Five Stars

Re: How to compare a date and a periode?

PersonneType.jpg

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)

 

Four Stars

Re: How to compare a date and a periode?

Thank you tdz, it works very well.