Newbie mapping question

[#1]

So... I have a list of items in my data-set, each of which belongs to a higher-level category. However, the categories are not in the data. I need to add a new field to each record in the data (onload) that takes it's value from a mapping table.

For example:

raw data:

Record	Item
1	car
2	apple
3	car
4	pear
5	boat
6	plane
7	apple
8	car
9	pear

mapping (lookup) table

Item	Category
car	Transport
apple	Fruit
pear	Fruit
boat	Transport
plane	Transport

Data after load:

Record	Item	Category
1	car	Transport
2	apple	Fruit
3	car	Transport
4	pear	Fruit
5	boat	Transport
6	plane	Transport
7	apple	Fruit
8	car	Transport
9	pear	Fruit

I've had a look at the online help on how to create the mapping table, and create a new field in which to populate it, but the peeny hasn't dropped yet

All help much appreciated.

Thanks...

[Nicole-Smith]

RawData:

LOAD Record, Item

FROM RawData;

LEFT JOIN (RawData)

LOAD * INLINE [

Item,Category

car,Transport

apple,Fruit

pear,Fruit

boat,Transport

plane,Transport

];

Note: For the RawData, replace that with the excel or SQL or wherever you're loading it from.  You may also need to replace the table name (as well as in the left join).

[swuehl]

MAP:

MAPPING LOAD Item, Category INLINE [

Item, Category

car, Transport

apple, Fruit

pear, Fruit

....

];

LOAD Record,

          Item,

          applymap('MAP', Item) as Category

FROM raw_data;

[#4]

Both very helpful, thanks.

One last thing to crack. What do I do if my Categories have a comma in them?

Thanks...

[swuehl]

Try quoting the value

LOAD A,B INLINE [

A, B

1, "Hi, all"

];

[#6]

Yeah, that's what I thought, too.

Actually, it's the ITEM that has commas in, not the category. I've tried quoting both, but it doesn't work.

Mapping load Item, Category Inline [

...

"Item with commas, and some brackets (here, as you can see)", "Category_A"

...

];

[swuehl · Accepted solution]

Seems to work for me for this simple sample:

MAP:

Mapping load Item, Category Inline [

Item, Category

"Item with commas, and some brackets (here, as you can see)", "Category_A"

];

DATA:

LOAD ApplyMap('MAP',Item) as Category, Item INLINE [

Item

"Item with commas, and some brackets (here, as you can see)"

];

Maybe your DATA Item values don't get read in as expected and therefore doesn't match your mapping?

[#8]

How odd.

I copied the field value back in and it worked. There must have been a typo somewhere.

Thanks for all your help.