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)
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...)
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...
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) + " ' "
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