Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 |
See if this post offers any help:
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
];
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 |
Yep. Whatever the requirement is, we'd be trying to add rows that describe that requirement to our mapping table.