Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a field that lists resources for activities. It lists them like this "Scaffold Builder, IQC" or "Mechanic, QC" or "Insulator, Civil Quality Control"
What I'm saying is I was a new field that will classify each row in my data by resource by looking at the contents of this string for in code style, i want to make a field that looks at each cell in the "resource" field and does this pseudo code
//Instring(ref to string to be searched, term to search for)
if( Instring([resource],"Scaff")) THEN [newresource]="scaff" else if(instring([resource],"Mech") then [newresource]="Mech"
I want to do this to each cell of data, so that the data is generalized instead of mixed in with QC resources.
Thanks for any help,
MZ
OK - got it I think.
Try this (untested): EDIT: Is tested and works!
Map_Translate:
MAPPING LOAD * INLINE [
MatchNo,Return
1,Scaffold Builder
2,Mechanic
3,Welder
4,Insulator
];
Data:
LOAD
[new field],
[old field],
ApplyMap('Map_Translate',WildMatch([old field],'*scaff*','*mech*','*weld*','*insul*'),'<Unmapped>') AS WildMatch
FROM
[New Field From Substrings.xlsx]
(ooxml, embedded labels, table is Sheet3);
See attached.
The Wildmatch() contains the substrings you want to look for and returns a number corresponding to the first one that matches. This number is then passed to the map and the mapped value is returned.
Hope this helps,
Jason
I'm not quite sure what you're asking for. Can you provide a few rows of sample data and how you want it to end up? Don't know wether to recommend SubStringCount() or SubField()...
Jason
Sure one second.
You could try mapping a field.
map1:
mapping load * inline [
x, y
1, one
2, two
3, three ] ;
ApplyMap ('map1', 2 ) returns ' two'
ApplyMap ('map1', 4 ) returns 4
ApplyMap ('map1', 5, 'xxx') returns 'xxx'
ApplyMap ('map1', 1, 'xxx') returns 'one'
ApplyMap ('map1', 5, null( ) ) returns NULL
ApplyMap ('map1', 3, null( ) ) returns 'three'
new field | old field |
Scaffold Builder | Scaffold Building, IQC, Janitor |
Scaffold Builder | Scaffold Builder, Labeler |
Mechanic | Labeler, Janitor, Mechanic |
Mechanic | Quality Control, Mechanic |
Welder | Quality Control, Welder |
Welder | Welder, Superintendent |
Insulator | Insulator, CIVIL MODS |
Insulator | IQC, INSULATOR |
Here's a demo I've attached. I want to search for substrings "Scaff", "Insul" or "Mech" and make a new field based on the results. If the adjacent cell contains "Scaff" i want the new fields value to be Scaffold Builder for example.
MZ
Apply map seems fine, but I'm putting this in a load statement. How can i use apply map won't let me enter the second argument as a field.
Map:
mapping load * inline [
old field, new field
'Scaffold Building, IQC, Janitor', Scaffold Builder
'Scaffold Builder, Labeler', Scaffold Builder
'Labeler, Janitor, Mechanic', Mechanic
'Quality Control, Mechanic', Mechanic
'Quality Control, Welder', Welder
'Welder, Superintendent', Welder
'Insulator, CIVIL MODS', Insulator
'IQC, INSULATOR', Insulator
];
[new table]:
load
applymap('Map',oldfield) as NewField
from table;
OK - got it I think.
Try this (untested): EDIT: Is tested and works!
Map_Translate:
MAPPING LOAD * INLINE [
MatchNo,Return
1,Scaffold Builder
2,Mechanic
3,Welder
4,Insulator
];
Data:
LOAD
[new field],
[old field],
ApplyMap('Map_Translate',WildMatch([old field],'*scaff*','*mech*','*weld*','*insul*'),'<Unmapped>') AS WildMatch
FROM
[New Field From Substrings.xlsx]
(ooxml, embedded labels, table is Sheet3);
See attached.
The Wildmatch() contains the substrings you want to look for and returns a number corresponding to the first one that matches. This number is then passed to the map and the mapped value is returned.
Hope this helps,
Jason
I'll try it right now.
Alright, It ALL make sense now.