tReplaceList - Whole Field? Need Advice...

One Star

tReplaceList - Whole Field? Need Advice...

Hello -
I don't mean to write a book, but as a newbie I am not fully sure of what my question should be.
I have data from a vendor which I have massaged to look like the following field:
ACCESSORIESCOMPUTER CASE ACCESSORIESCOOLING FANS;
I am using a tReplaceList which looks up the values of what the category should be based on a category mapping table:
MapID
CategoryIn (Expected input, from example above)
AssignedCategory (1:n from the Category table PK)
I have another vendor which used 3 letter codes, and this procedure worked perfect. However, with this vendor I am getting strange results.
My current output is:
AC2SSORIE27OMPUTER CASE AC2SSORIE27OOLING 6S;
To me, it seems like the tReplaceList is finding the 3 letter codes as well and replacing it, for example the first word is picking up the "CE" in ACCESSORIES and replacing it with the number 2, which is the code CE for the category "Consumer Electronics".
I need the tReplaceList to evaluate the entire field so that the input:
ACCESSORIESCOMPUTER CASE ACCESSORIESCOOLING FANS;
is replaced with (in this example)
6; the category code for Fans
A complete input example (some fields truncated for space)
Input:
VendorID;VendorPartNum;MFR;MFRPartNum;Category;<MORE FIELDS>
LT;CM-FAN-120MM-BL;COOLER MASTER;;ACCESSORIESCOMPUTER CASE ACCESSORIESCOOLING FANS;<MORE DATA>
Output
VendorID;VendorPartNum;MFR;MFRPartNum;Category;<MORE FIELDS>
LT;CM-FAN-120MM-BL;COOLER MASTER;;6;<MORE DATA>
How can I do this -
Thanks!
Wayne P.
Community Manager

Re: tReplaceList - Whole Field? Need Advice...

Hello
Here is a demo job,
in.csv:

VendorID;VendorPartNum;MFR;MFRPartNum;Category;<MORE FIELDS>
LT;CM-FAN-120MM-BL;COOLER MASTER;;ACCESSORIESCOMPUTER CASE ACCESSORIESCOOLING FANS;<MORE DATA>

Result:
Starting job forum10191 at 17:21 12/03/2010.
connecting to socket on port 3798
connected
.--------+---------------+-------------+----------+--------+-----------.
| tLogRow_1 |
|=-------+---------------+-------------+----------+--------+----------=|
|VendorID|VendorPartNum |MFR |MFRPartNum|Category|MORE_FIELDS|
|=-------+---------------+-------------+----------+--------+----------=|
|LT |CM-FAN-120MM-BL|COOLER MASTER| |6 |<MORE DATA>|
'--------+---------------+-------------+----------+--------+-----------'
disconnected
Job forum10191 ended at 17:21 12/03/2010.

Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: tReplaceList - Whole Field? Need Advice...

Thanks for your example.
You showed everything I have, except for use of the tFixedFlowInput. I do not see that component in the component manual. Your example with the tFixedFlowInput seems to hard code the lookups. I need them dynamic, based on the database lookup.
See my attached screenshots.
Community Manager

Re: tReplaceList - Whole Field? Need Advice...

Hello
Yes, I just take an example, you can add the lookup flow from any data source. About tReplaceList component, it will compare the whole lookup search column as a string with the main replace column, if the main replace column contains this string, the string will be replace with corresponding lookup replacement column.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: tReplaceList - Whole Field? Need Advice...

Hello
Yes, I just take an example, you can add the lookup flow from any data source. About tReplaceList component, it will compare the whole lookup search column as a string with the main replace column, if the main replace column contains this string, the string will be replace with corresponding lookup replacement column.

Shong,
Could you please explain what I did wrong?
The way I setup the job, the tReplaceList is not behaving like this.
Thanks -
One Star

Re: tReplaceList - Whole Field? Need Advice...

This http://www.talendforge.org/forum/viewtopic.php?id=10238 seems to be a similar problem in some respects.
One Star

Re: tReplaceList - Whole Field? Need Advice...

I've logged this as a bug as I'm getting the same behavior.
Regards
David
Community Manager

Re: tReplaceList - Whole Field? Need Advice...

Hello wpedersen
The lookup search column should be row2.AssignedCategory and the lookup replacement column should be row2.CategoryIn.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: tReplaceList - Whole Field? Need Advice...

Hello wpedersen
The lookup search column should be row2.AssignedCategory and the lookup replacement column should be row2.CategoryIn.

When I do this, no change is made to the data. The way I named my files and expect input, CategoryIn contains the data we are searching for in the file, the input. AssignedCategory is the data we want to replace in the file.
So, when I reverse it, effectively no matches are found in the replacement list, and the data remains unchanged.
I have to agree that it seems to be a bug.
Thanks -
Wayne
Community Manager

Re: tReplaceList - Whole Field? Need Advice...

Hello Wayne
Are you working on Perl project or Java project?
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: tReplaceList - Whole Field? Need Advice...

Hello Wayne
Are you working on Perl project or Java project?

Java project
Community Manager

Re: tReplaceList - Whole Field? Need Advice...

Hello
Ok, please export your records from db to a file, and then send me your job with the main file and the lookup file via email.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: tReplaceList - Whole Field? Need Advice...

Thanks - Email is enroute.
Wayne