Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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!
something going wrong please check for extra spaces
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
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