We recently upgraded from 11.2 to 12.1 and are having issues with smart parsing not being so smart.
The issue starts off with a field normally in the format of;
Single alphabetic character, Symbol(either -,= or +), Single numeric character, Symbol(either -,= or +)
eg. R+1=
The symbols are also not always present, so it could be just R2 or R2- or R=3
This field is then parsed into 2 parts the Alphabetic character and first symbol and the Numeric character and second symbol. This is where the problems start. The numeric character and symbol (or no symbol) is categorical data (can be ranked with the symbol being subdivisions in the order of -,=,+) but gets smart parsed as numeric. This is wrong because the values without any symbol then get defined as being + when they are actually closer to being = . They also get displayed with the + when it is not there in the original field.
To complicate it more we also want to use the split fields in a dual function to rank the categories and allow us to calculate average categories when making selections in the app. These are on 15 point scales, using mapping tables to map the numeric part to the 'text'.
This all worked perfectly in 11.2 by the way.
So code to reproduce this;
Sample QVD with something like;
ID, Grade
94, R-5-
95, R-5+
96, R-5=
97, R+1+
98, R+2-
99, R+2+
100, R+2=
101, R+3-
102, R+3+
103, R+3=
104, R+4-
105, R+4+
106, R+4=
107, R+5-
108, R+5+
109, R=1=
110, R=2-
111, R=2+
112, R=2=
113, R=3-
114, R=3+
115, R=3=
116, R=4-
117, R=4+
118, R=4=
119, R=5-
120, R1
121, R2
122, R3
123, R4-
124, R4+
125, R5-
126, U-2
127, U-2-
128, U-2+
129, U-2=
130, U-3
131, U-3-
132, U-3+
133, U-3=
134, U-4-
135, U-4+
136, U-4=
137, U-5-
138, U-5+
139, U+1=
140, U+2
141, U+2+
142, U+2=
143, U+3
(I think this captures all possible combination types of including symbols or not)
and QV script with mapping tables;
alpha_scale:
mapping
LOAD * INLINE [
MapFrom,MapTo
E+,15
E=,14
E,13.5
E-,13
U+,12
U=,11
U,10.5
U-,10
R+,9
R=,8
R,7.5
R-,7
O+,6
O=,5
O,4.5
O-,4
P+,3
P=,2
P,1.5
P-,1
];
num_scale:
LOAD * INLINE [
MapFrom, MapTo
1-, 1
1=, 2
1, 1.5
1+, 3
2-, 4
2=, 5
2, 4.5
2+, 6
3-, 7
3=, 8
3, 7.5
3+, 9
4-, 10
4=, 11
4, 10.5
4+, 12
5-, 13
5=, 14
5, 13.5
5+, 15
];
Table:
Load
ID,
if(match(Mid(Grade,2,1),'-','+','='),Left(Grade,2),Left(Grade,1)) as Alpha,
if(match(right(Grade,1),'-','+','='),Right(Grade,2),Right(Grade,1)) as Numer,
dual(if(match(Mid(Grade,2,1),'-','+','='),Left(Grade,2),Left(Grade,1)),applymap('alpha_scale',if(match(Mid(Grade,2,1),'-','+','='),Left(Grade,2),Left(Grade,1))))as Alpha_Dual,
dual(if(match(right(Grade,1),'-','+','='),Right(Grade,2),Right(Grade,1)),applymap('num_scale',if(match(right(Grade,1),'-','+','='),Right(Grade,2),Right(Grade,1)) )) as Numer_Dual
From table.qvd;
I've tried a few different methods of wrapping in text() etc, also loading the num_scale mapping table as a table then resident loading with a text() wrap on the mapfrom (this is probably the closest to correct I have gotten) but each time it corrects one thing it seems to break somewhere else so here I am. Any help or even explanation on what exactly is happening with this new 'smart' parsing would be helpful. If I can understand it maybe I can see a way around it...
