topic Re: Excel vLookup in Talend in Talend Studio

Excel vLookup in Talend

LennartSchulte — Mon, 13 Jul 2020 20:55:58 GMT

Hi,

i was now searching and trying for some time and was not able to find a solution for an obviously simple problem. I want to perform an vLookup like mapping in talend.

This is what i have so far:

Master File:

uid

name

description

....

Lookup File:

parentUID(refers to UID in Master file)

attributeID

value

Target File

"ID": master.uid

"Name: master.name

"Description": master.description

"Image": lookup.value (link via lookup.parentUID and master.UID and filter for an specific attributeID like "2388")

"TDS": lookup.value (link via lookup.parentUID and master.UID and filter for an specific attributeID like "2547")

"PDS": lookup.value (link via lookup.parentUID and master.UID and filter for an specific attributeID like "157")

I assume for someone who's familiar with talend this is a dummy question but as i learn talend by doing and try an error and never had this usecase befor i have no clue on what to do after trying/searching for 6 hours.

Re: Excel vLookup in Talend

LennartSchulte — Mon, 13 Jul 2020 21:09:21 GMT

Or do i need to use the LookUp File multiplte times to have one manual lookup with a fixed filter on attributeID as much as i want to extract attributes from the lookup?

Re: Excel vLookup in Talend

Anonymous — Tue, 14 Jul 2020 03:05:20 GMT

@Lennart Schulte

How to know the specific attributeID used for the filter for each record of Master file? Can you please explain it?

Regards

Shong

Re: Excel vLookup in Talend

LennartSchulte — Tue, 14 Jul 2020 04:18:37 GMT

Hi Shong,

many thanks for your answer.

The attributeID is given/defined manually. The task is to select for an horizontal file (a specific column) a specific attribute value out of an vertical list which contains all attributes of the product.

Re: Excel vLookup in Talend

Anonymous — Wed, 15 Jul 2020 09:48:30 GMT

I am afraid I can't still understand what are your expected result, it is better to explain your requirement with some example data. @Lennart Schulte ,

"Image": lookup.value (link via lookup.parentUID and master.UID and filter for an specific attributeID like "2388")

Re: Excel vLookup in Talend

vikramk — Thu, 16 Jul 2020 14:04:28 GMT

If you need the result only for the specific attributeID then, you can perform normal left join in tmap. Then create a variable in tmap with filter conditions for required values and then map this variable to your output column in tmap

Re: Excel vLookup in Talend

LennartSchulte — Thu, 16 Jul 2020 15:11:11 GMT

i'm afraid i don't know how to do that. can you provide me a link on how to do that? google wasn't able to help me with this either 😉

Re: Excel vLookup in Talend

LennartSchulte — Thu, 16 Jul 2020 15:55:28 GMT

I hope the screen helps. so i want to fill an horizontal sheet in the end based on a vertical list of attributes containing all attributes of a product (in this case ID 72). and from this big list of attributes i just want to pick specific ones.

Re: Excel vLookup in Talend

LennartSchulte — Thu, 16 Jul 2020 15:55:33 GMT

Re: Excel vLookup in Talend

vikramk — Fri, 17 Jul 2020 05:05:52 GMT

I think this you tube video might help for your requirement for creating variable in tmap

https://www.youtube.com/watch?v=UbPU7VVu-fU

Please use filter logics in variables for each value and assign it to your output column.

please refer below document for more on tmap:

https://help.talend.com/reader/wDRBNUuxk629sNcI0dNYaA/FzPVnU_ZpTs4X4nh4BfGaQ

Re: Excel vLookup in Talend

Anonymous — Fri, 17 Jul 2020 08:00:23 GMT

I still have a question, the attributeID for image column is always 2338 and it is known before you execute the job, or it comes from lookup file and you don't know what it is before you execute the job?

Re: Excel vLookup in Talend

LennartSchulte — Fri, 17 Jul 2020 08:23:36 GMT

yes. the id's are fixed and known while setting up the job.