how to use a date range in tMap Join

[Anonymous]

Hi
I have a first table with main row DATE, BU, PRODUCT, VALUE, ....
In my lookup table I have BU, PRODUCT, DATE_BEGIN, DATE_END, CATEGORY, ....
Others Lookup tables
In my output flow I want to have
DATE, BU, PRODUCT, CATEGORY, VALUE, others fields from main, from first lookup, from other lookup
The CATEGORY depends on a Join on PRODUCT and (DATE >= DATE_BEGIN && DATE <= DATE_END)
How could I define this in the tMap
(I need to use tMap because I have more tables, fields and tranformations)

[Anonymous]

you can use the tintervalmatch

[Anonymous]

you can have a main link and a lookup table in a tintervalmatch, that's a dynamic component. can you show me a screenshot of your tmap?

[Anonymous]

you can have a main link and a lookup table in a tintervalmatch, that's a dynamic component. can you show me a screenshot of your tmap?

This schema represents an idea of what I'm looking for, but I'm not sure the expression filter is working

[Anonymous]

I've tested with this case
Sales file: tst01_vente.csv (see result)
Product file : tst01_produit.csv
produit categorie dte_debut dte_fin
H107 H107A7Q1 01/01/2007 31/03/2007
H107 H107A7Q2 01/04/2007 30/06/2007
H107 H107A7Q3 01/07/2007 30/09/2007
H107 H107A7Q4 01/10/2007 31/12/2007
H107 H107A8Q1 01/01/2008 31/12/2008
H108 H108A7Q1 01/01/2007 31/03/2007
H108 H108A7Q2 01/04/2007 30/06/2007
H108 H108A7Q3 01/07/2007 30/09/2007
H108 H108A7Q4 01/10/2007 31/12/2007
H108 H108A8Q1 01/01/2008 31/12/2008
H109 H109A7Q1 01/01/2007 31/03/2007
H109 H109A7Q2 01/04/2007 30/06/2007
H109 H109A7Q3 01/07/2007 30/09/2007
H109 H109A7Q4 01/10/2007 31/12/2007
H109 H109A8Q1 01/01/2008 31/12/2008

And with the tmap (see picture)
It's working but I would like to be sure the join works correcly
PS: I know the tIntervalMatch works for my case but I need to do others things with tMap (other lookup, other transformations...)

This is the result
H107|15/01/2007|H107A7Q1
H107|15/02/2007|H107A7Q1
H107|15/03/2007|H107A7Q1
H107|15/04/2007|H107A7Q2
H107|15/05/2007|H107A7Q2
H107|15/06/2007|H107A7Q2
H107|15/07/2007|H107A7Q3
H107|15/08/2007|H107A7Q3
H107|15/09/2007|H107A7Q3
H107|15/10/2007|H107A7Q4
H107|15/11/2007|H107A7Q4
H107|15/12/2007|H107A7Q4
H107|15/01/2008|H107A8Q1
H107|15/02/2008|H107A8Q1
H108|15/01/2007|H108A7Q1
H108|15/02/2007|H108A7Q1
H108|15/03/2007|H108A7Q1
H108|15/04/2007|H108A7Q2
H108|15/05/2007|H108A7Q2
H108|15/06/2007|H108A7Q2
H108|15/07/2007|H108A7Q3
H108|15/08/2007|H108A7Q3
H108|15/09/2007|H108A7Q3
H108|15/10/2007|H108A7Q4
H108|15/11/2007|H108A7Q4
H108|15/12/2007|H108A7Q4
H109|15/01/2008|H109A8Q1
H109|15/02/2008|H109A8Q1
H109|15/01/2007|H109A7Q1
H109|15/02/2007|H109A7Q1
H109|15/03/2007|H109A7Q1
H109|15/04/2007|H109A7Q2
H109|15/05/2007|H109A7Q2
H109|15/06/2007|H109A7Q2
H109|15/07/2007|H109A7Q3
H109|15/08/2007|H109A7Q3
H109|15/09/2007|H109A7Q3
H109|15/10/2007|H109A7Q4
H109|15/11/2007|H109A7Q4
H109|15/12/2007|H109A7Q4
H109|15/01/2008|H109A8Q1
H109|15/02/2008|H109A8Q1

[Anonymous]

Hi,
I'm working on nothing similar and experiencing the same difficulties.
In input, i've got a file containing (for now) a unique row :
INC | MATRI | DATEDEB | DATEFIN
1 | 68 | 24/03/08 |27/03/08
This input file is connecting to a Tmap (see the attach picture) that is looking in a table (MySQL Table) if it can find the MATRI field. If the MATRI field (here : 68) is found in the table, i've got a filter that verify that the DATEDEB field from the input file is a date after the DATEDEB field in the table and vice versa for the DATEFIN (DATEEND).
For the MATRI value "68", i've got two rows matching this Matri value in the table but only one is matching my filter. In results, i ve got one row accepted and one row rejected.
This is not what i was expecting, I was thinking that the filter was going to work like a where wlause in a sql request. But if the filter was working like the "where" clause in a sql request, we should have only one row returned : the good one. If none of the rows were matching, then i should send the row from my input field in the rejects table.
To sum up, the Tmap is not making a full join : First, the Tmap makes the join on the criteria Matri and then it applies the filter. The filter is not considered like a where clause.
Because of this behavior, i think that, maybe, the return of your join, PAULO, is not totally good. Or, maybe the return is godd but not the rejects if you have a rejects table in your tMap. (Well... maybe i'm wrong)
Is there a way to make a full job (including a where clause) ?
regards,
PS : i hope i've been clear enough...

[_AnonymousUser]

Do you solution this post? I have similar problem.
Thanks

[Anonymous]

If your input is a databse, try creating 2 contexts
ie
context.date_start
context.date_end
and then just add this query to the end
WHERE usage_date >= '" + TalendDate.formatDate("yyyy-MM-dd HH:mm:ss",context.date_start) + "' AND usage_date <= '" + TalendDate.formatDate("yyyy-MM-dd HH:mm:ss",context.date_end) + " ' "

my usage_date = your DATE

this works for me in mysql

[Anonymous]

or you can make the full join in the tMap and then using the output filter, filter out the rows that do not fall within your expected interval.

[_AnonymousUser]

Hi,
similar problem I had:
match a ZIP code against an interval of zip (e.g. from 1000-1999).
Lucky enough those can be used as INTEGER.
So what I did is follow your advice with the DB for the reference data (ZIP-ranges).
Then in an input-component I put a query like:
"SELECT
reference,
zip_from,
zip_to
FROM zip_ref
WHERE zip_from <= " + context.zip + " AND zip_to >= " + context.zip

Thanks for the hint with context variable!