topic Re: Smart Parsing Issue in QlikView

Smart Parsing Issue

tcullinane — Fri, 18 Aug 2017 13:28:01 GMT

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

Re: Smart Parsing Issue

sunny_talwar — Fri, 18 Aug 2017 13:52:34 GMT

Would you be able to share few Grade values from table.qvd?

Re: Smart Parsing Issue

tcullinane — Fri, 18 Aug 2017 14:00:16 GMT

HI Sunny, The grade vales are the R+1=, etc values or the combined field before parsing, the ids in the sample are the range 94 to 143

Re: Smart Parsing Issue

effinty2112 — Sat, 19 Aug 2017 11:00:13 GMT

Hi Thomas,

This script gives the alpha_scale and num_scale you're looking for:

Sample:

Load

Match(Mid(Grade,1,1),'P','O','R','U','E')*3 - 2

+ Pick(Match(Mid(Fourchar,2,1),'-','_','=','+'),0,0.5,1,2) as alpha_scale,

Mid(Fourchar,3,1)*3-2 + Pick(Match(Mid(Fourchar,4,1),'-','_','=','+'),0,0.5,1,2) as num_scale;

Load

ID,

Grade,

Pick(len(Grade)-1,

Mid(Grade,1,1) & '_' & Mid(Grade,2,1) & '_' ,

if(IsNum(Mid(Grade,2,1)),

Mid(Grade,1,1) & '_' & Mid(Grade,2,2),

Mid(Grade,1,2) & Mid(Grade,3,1) & '_')

Grade) as Fourchar;

LOAD * Inline [

ID, Grade

94, R-5-

95, R-5+

96, R-5=

97, R+1+

98, R+2-

99, R+2+

100, R+2=

142, U+2=

143, U+3

];

These are some of the results:

ID	Grade	alpha_scale	num_scale
94	R-5-	7	13
95	R-5+	7	15
96	R-5=	7	14
97	R+1+	9	3
98	R+2-	9	4
99	R+2+	9	6
100	R+2=	9	5

Cheers

Andrew

Re: Smart Parsing Issue

antoniotiman — Sat, 19 Aug 2017 11:42:17 GMT

Hi Thomas,

try this

alpha_scale:
mapping LOAD MapFrom,'{'&MapTo&'}' as MapTo INLINE [
MapFrom,MapTo
E+,15
.......

P-,1 ];

num_scale:
Mapping LOAD MapFrom,'<'&MapTo&'>' as MapTo INLINE [
MapFrom, MapTo
1-, 1
1=, 2
......

5+, 15 ];

LOAD *,TextBetween(MapSubString('alpha_scale',Grade),'{','}') as Alpha,
TextBetween(MapSubString('num_scale',Grade),'<','>') as Numer Inline [
ID, Grade
94, R-5-
...

141, U+2+
142, U+2=
143, U+3 ];

See Attachment,

Regards,

Antonio

Re: Smart Parsing Issue

tcullinane — Mon, 21 Aug 2017 10:43:39 GMT

nice suggestion but unfortunately doesn't work for the 'Numer'

Any '1' or '1+' (or '2' and '2+' etc) get mapped tot he same value so ends up something like

Grade	Alpha	Numer

O-1	4	1.5
O-1-	4	1
O-1+	4	1.5
O-2	4	4.5
O-2-	4	4

Re: Smart Parsing Issue

tcullinane — Mon, 21 Aug 2017 11:19:36 GMT

This works.

I dislike the calculated values for the final fields though, makes it a bit less obvious as to what happening, with the inline mapping table its easier for anyone to see this is what that should be etc. So using your intermediate step to give everything a fourcharachter grade along with mapping does the trick.