topic Re: how to use a date range in tMap Join in Talend Studio

how to use a date range in tMap Join

Anonymous — Sat, 16 Nov 2024 14:24:37 GMT

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)

Re: how to use a date range in tMap Join

Anonymous — Tue, 01 Apr 2008 11:35:56 GMT

you can use the tintervalmatch

Re: how to use a date range in tMap Join

Anonymous — Tue, 01 Apr 2008 11:53:51 GMT

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?

Re: how to use a date range in tMap Join

Anonymous — Tue, 01 Apr 2008 17:19:53 GMT

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

Re: how to use a date range in tMap Join

Anonymous — Wed, 02 Apr 2008 10:50:12 GMT

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

Re: how to use a date range in tMap Join

Anonymous — Thu, 03 Apr 2008 15:17:31 GMT

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...

Re: how to use a date range in tMap Join

_AnonymousUser — Mon, 14 Feb 2011 20:25:47 GMT

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

Re: how to use a date range in tMap Join

Anonymous — Tue, 15 Feb 2011 06:39:36 GMT

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

Re: how to use a date range in tMap Join

Anonymous — Tue, 15 Feb 2011 14:25:52 GMT

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.

Re: how to use a date range in tMap Join

_AnonymousUser — Thu, 13 Oct 2011 21:46:22 GMT

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!