Combining Values from a Field

[#1]

Hi-

I have a ItemId code field that corresponds to an ItemName field.  The ItemID field can have more than one ItemName.  My problem is that I want this relationship to be 1 to 1.  Is there a way to combine the ItemNames so that after I load the data, if I select an ItemID that has more than 1 ItemName, it only shows 1 ItemName?

Thank you

[swuehl · Accepted solution]

You can use a group by load and one of the aggregation functions, for example Minstring(), to pick one of the ItemName values per ItemID. The question is, which one?

edit:

LOAD

ItemID,

minstring(ItemName) as OnlyOneItemName

resident TABLE group by ItemID;

[MayilVahanan]

HI

ItemTest:

Load * Inline

[

ItemId,ItemName

1,aaa

1,bbb

1,ccc

2,ddd

2,eee

2,fff

];

load ItemId,MaxString(ItemName) as IName Resident ItemTest Group by ItemId;

Hope it helps

[#4]

The ItemName that is chosen is arbitrary, as they have the same meaning.

Example, an ItemName that has two values.  First, called Item1.  Second value called Item(1).

[#5]

Hi-

The ItemName and ItemID fields contain thousands of values, so I'd rather not go through and list each item Inline as per the suggestion.

[MayilVahanan]

Hi

For example, i used like that..

try like this

Load ItemNo,MaxString(ItemName) as ItemName from table group by ItemNo;

Hope that helps

[#7]

If ItemID is in Table1, and ItemName is in Table2, how then would I script that?

[swuehl]

There is a linking field between ItemID and ItemName, connecting the two tables, right?

In a simple case, just use this field to group by your Table2.

Can you tell us a bit more about your table structure?

[#9]

I had to join a couple of tables in order to get the structure correct.  Once I did that, your original solution worked perfectly.

Thank you.