topic Newbie mapping question in QlikView

Newbie mapping question

Fri, 27 Dec 2013 14:49:33 GMT

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...

Re: Newbie mapping question

Nicole-Smith — Fri, 27 Dec 2013 14:54:57 GMT

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).

Re: Newbie mapping question

swuehl — Fri, 27 Dec 2013 14:55:03 GMT

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;

Re: Newbie mapping question

Fri, 27 Dec 2013 15:18:16 GMT

Both very helpful, thanks.

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

Thanks...

Re: Newbie mapping question

swuehl — Fri, 27 Dec 2013 15:20:51 GMT

Try quoting the value

LOAD A,B INLINE [

A, B

1, "Hi, all"

];

Re: Newbie mapping question

Fri, 27 Dec 2013 15:29:40 GMT

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"

...

];

Re: Newbie mapping question

swuehl — Fri, 27 Dec 2013 15:40:21 GMT

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?

Re: Newbie mapping question

Fri, 27 Dec 2013 15:48:22 GMT

How odd.

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

Thanks for all your help.