Multiple field mapping table

[jessica_webb]

Hi,

Apologies if the question title isn't clear, but I'm not entirely sure what it is I'm looking for. A mapping table seemed the nearest starting point, but I know I can't create a mapping table with more than two columns.

In my data, I have two columns named 'Characteristic' and 'Characteristic Value' - the values in each column come directly from SQL so are in boolean format.

What I need is a corresponding text value to be created during the load (so that I can later filter by these text values).

Example:

Characteristic	Text value	Characteristic value	Text value
0	All	0	All
1	Gender	0	Male
		1	Female
2	FSM	0	FSM
		1	Non-FSM
11	SEN	0	N
		1	A
		2	P
		3	S

So, the first and third columns are what's in the data, and the second and fourth (italicised) are what I want to show.

A mapping table would work for the characteristic column, but obviously it wouldn't for the characteristic value column as 0, 1 and 2 can mean different things.

I want to avoid editing the original excel file (which is what we currently do) - does anyone have any suggestions how I can do this?
Many thanks,
Jess

[hic · Accepted solution]

I would use the following in the script:

MapData:
Load * inline
[Characteristic, TextValue1, CharacteristicValue, TextValue2
0,All,0,All
1,Gender,0,Male
1,Gender,1,Female
2,FSM,0,FSM
2,FSM,1,Non-FSM
11,SEN,0,N
11,SEN,1,A
11,SEN,2,P
11,SEN,3,S];

Map1:
Mapping Load distinct Characteristic, TextValue1 Resident MapData;
Map2:
Mapping Load distinct Characteristic &'|'& CharacteristicValue as Input, TextValue2 Resident MapData;

and then I can call the map tables by using

Applymap('Map1', x , Null()) as NewField1,

Applymap('Map2', x &'|'& y, Null()) as NewField2,

HIC

[jessica_webb]

Hi Henric,

Thanks so much for your response. Have implemented your suggestions, but I'm not quite there yet...

I have place the following section in a tap named 'Mapping':

MapData:
Load * inline
[Characteristic, TextValue1, CharacteristicValue, TextValue2
0,All,0,All
1,Gender,0,Male
1,Gender,1,Female
2,FSM,0,FSM
2,FSM,1,Non-FSM
11,SEN,0,N
11,SEN,1,A
11,SEN,2,P
11,SEN,3,S];

Map1:
Mapping Load distinct Characteristic, TextValue1 Resident MapData;
Map2:
Mapping Load distinct Characteristic &'|'& CharacteristicValue as Input, TextValue2 ResidentMapData;

And have then placed the following in my main load script as this:

LOAD Location,

    Institution,

    Characteristic,

    Applymap('Map1', Characteristic , Null()) as NewField1,

    CharacteristicValue,

    Applymap('Map2', Characteristic &'|'& CharacteristicValue, Null()) as NewField2,

    Result

FROM

(ooxml, embedded labels, table is Sheet1);

When I reload the script, there are no errors, but when I create a listbox for 'NewField1', it's blank.

What am I doing wrong?

Thanks,

Jess

P.S. Apologies for my formatting - I don't know how to enter script in here in the proper style!

[sujeetsingh]

something going wrong please check for extra spaces

[hic]

It's impossible to tell without seeing the data.

It could perhaps be a formatting issue. Try formatting the numbers, like

   Applymap('Map2', Num(Characteristic,'0') &'|'& Num(CharacteristicValue,'0'), Null()) as NewField2,

instead.

HIC

[jessica_webb]

Completely my error - still get quite confused over Apply Map!

It was where I had the following:

Characteristic,

    Applymap('Map1', Characteristic , Null()) as NewField1,

    CharacteristicValue,

    Applymap('Map2', Characteristic &'|'& CharacteristicValue, Null()) as NewField2,

I was basically loading the same fields twice!

Have now loaded as this:

Characteristic,

    Applymap('Map1', Characteristic , Null()) as NewField1,

    CharacteristicValue,

    Applymap('Map2', Characteristic &'|'& CharacteristicValue, Null()) as NewField2,

And it works perfectly. Thank you so much for you help - it was the perfect solution!

Jess