topic Text Modification in QlikView

Text Modification

brooksc57 — Fri, 20 May 2016 18:20:45 GMT

I have coded this in an Excel VBA macro, but am looking for a way to do it within the script editor in qv.

as a reference these are part numbers. The reason for even doing this is the lookup "clean" parts because end users slap the dashes and slashes in the wrong place sometimes...of course this whole thing leads to other issues, but their are checks in place for that.

anyway trying to solve this part number modification.

XY-123 should become XY123 --- because letters and numbers can touch and the dash can be "removed" or not kept.

and

X1-23Y should remain X1-23Y ---- because two numbers cannot touch, if they already had a dash between them

and

X-Y123 should remain X-Y123 ---- because two letters cannot touch, if they already had a dash between them

in excel vba I grab the length of a field and read 3 characters at a time and make changes...in this case keeping or not keeping the dash based on istext or isum of the 1st and 3rd characters. in excel vba I do this for a field row by row.

as you'd guess there are lot more rules than the one above, but any help with this one would get me close to solving the others.

thx

brooks

Re: Text Modification

sunny_talwar — Fri, 20 May 2016 18:36:48 GMT

See if this post offers any help:

Finding text patterns in Data Columns

Re: Text Modification

johnw — Fri, 20 May 2016 21:36:18 GMT

You can do it by generating a substring map.

Undash:
MAPPING LOAD
L & '-' & N as From
,L & N as To
;
LOAD
chr(ord('A')+recno()-1) as L
,text(iterno()-1) as N
AUTOGENERATE 26
WHILE iterno() <= 10
;

Main:
LOAD
Something
,mapsubstring('Undash',Something) as SomethingFixed
INLINE [
Something
XY-123
X1-23Y
X-Y123
];

Re: Text Modification

swuehl — Fri, 20 May 2016 22:21:49 GMT

What about

X1-Y123

Should this become X1Y123, numbers and letters can touch?

Maybe we need to expand the mapping table to contain also the combinations with L & N exchanged:

Undash:

MAPPING

LOAD

If(FirstRun, L & '-' & N , N & '-' & L) as From

,If(FirstRun, L & N, N & L) as To

;

LOAD

Div(Iterno(),10) as FirstRun,

chr(ord('A')+recno()-1) as L

,text(Mod(iterno()-1,10)) as N

AUTOGENERATE 26

WHILE iterno() <= 20

;

Something	SomethingFixed
X-Y123	X-Y123
X1-23Y	X1-23Y
X1-Y123	X1Y123
XY-123	XY123

Re: Text Modification

johnw — Fri, 20 May 2016 22:59:35 GMT

Yep. Whatever the requirement is, we'd be trying to add rows that describe that requirement to our mapping table.