Compound Look up fields

[Former Member]

Hello experts,

I am working on a requirement where my source field - Name (lookup(flat) to details table(flat))

I have

Table1

1) Dept - Text - DF

2) Name - lookup(flat) - table2

Table 2

1) Firstname (DF)

2) Lastname (DF)

Table 2 values

john smith

scott kline

Here as i am trying to look in to 2 display fields i can see the values in value mapping as

john, smith

scott, kline

After going throgh some documents i came to know that this can be achived by "compund field look up" ,

Can any expert please help me in executing my issue !!!

Regards

Phani

[Former Member · Accepted solution]

Hi,

Whenever your table has more than one display field, both the fields say FirstName and LastName will appear in D(first column in Mapping Fields) in Import Manager. In order to create the Compound field, first map FirstName from the source file with the FirstName field of the repository which has D in front of it in Import Manager. Similarly map the LastName field. Now select both the fields at source side, right click and select Create Compound Field. This will automatically create the Compound Field and map this newly created field with the Destination Field. Just check the Value Mapping of this compound field.

Regards,

Jitesh Talreja

[Former Member · Accepted solution]

Hi Jitesh,

Thanks for the response.

as per your suggestion..

This time i did NOT fill my look up table values first..

i directly selected my excel at source end which has

firstname | lastname

john smith

scott kline

and seleted table 2 at destination.

mapped "In order to create the Compound field, first map FirstName from the source file with the FirstName field of the repository which has D in front of it in Import Manager. Similarly map the LastName field. Now select both the fields at source side, right click and select Create Compound Field. This will automatically create the Compound Field and map this newly created field with the Destination Field."

i have defined value mapping..and imported the data.

Now, i can see in data manager the value in my look up fied as smith, john and kline, scott in the 2 records which werre just uploaded ??actually did not solve my problem, i wish to see john smith and kline line with out any ',' in between them..

can you please help me

********************

I have

Table1

1) Dept - Text - DF

2) Name - lookup(flat) - table2

Table 2

1) Firstname (DF)

2) Lastname (DF)

Table 2 values

john smith

scott kline

Here as i am trying to look in to 2 display fields i can see the values in value mapping as

john, smith

scott, kline

*********************

[Former Member · Accepted solution]

Hi Phani

display fields are always shown in Data manager with a delimiter ; in this case.

If your requirement is to see concatenated values of First Name and Last Name define a 3rd field FULL NAME in the data model as type text and as a calculated field. In the calculation expression write COncatenate( First Name, Last Name)

Now load the repositories and you can see FULL NAME having the values as desired.

best regards

Ravi

[Former Member · Accepted solution]

hello,

when i try as you mentioned,

1) i need to remove DF for both the fields (firstname and lastname) and maintain DF only for Full name, But my table should have couple of other fields with DF , Can you suggest me...

2) when i use concatenate i get my o/p as (eg: john;smith & scott;kline ), can we make this as "" john smith "" & ''' scott kline ""

Could you please help me..

[Former Member · Accepted solution]

Hi Phani

Use the following expression for the calculated field.

Concatenate(First name; " "; Last name)

Make only Full Name field as the Dispaly field and both First name and Last Name should be non display fields.

hope this helps.

regards

Ravi

[Former Member · Accepted solution]

Hi,

Whenever you use Concat it will insert ";" in between. In order to get the desired result create the expression as shown below

FIRSTNAME & " " & LASTNAME

Note: Dont write anything by own while writing expressions, select from the list available. Just put space in " ".

Regards,

Jitesh Talreja

[Former Member · Accepted solution]

Hi Phani

As suggested by Jitesh use & in the formula for removing the ;

Please close the thread if answered.

best regards

Ravi

[Former Member · Accepted solution]

hello experts,

Great, that worked. Thanks a lot.

Between, this will work only with 1 display field in my look up table.But i do have couple of Other fields with DF (which was my first question in the thread)!!

My lookup table looks like...

First name

Lastname

Full name DF (i used concat here Firstname, lastname)

Department DF

Organisation DF

maintable:

Cid

Fullname (Lookupflat)

dept (Lookupflat)

org (Lookupflat)

Here in IM in Value mapping i can see the values of department and organisation too when i wish to select fullname only..!!!

Could any one help me on this issue..

[Former Member · Accepted solution]

Hi,

Can you please explain why you have made Department and Organization as the Display fields?

Regards,

Jitesh Talreja