Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Make a new field based on string content of other field

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

1 Solution

Accepted Solutions
Jason_Michaelides
Luminary Alumni
Luminary Alumni

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

View solution in original post

11 Replies
Jason_Michaelides
Luminary Alumni
Luminary Alumni

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

Not applicable
Author

Sure one second.

goldnejea8
Partner - Creator
Partner - Creator

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'

Not applicable
Author

new fieldold field
Scaffold  BuilderScaffold Building,  IQC, Janitor
Scaffold  BuilderScaffold Builder,  Labeler
MechanicLabeler, Janitor,  Mechanic
MechanicQuality Control,  Mechanic
WelderQuality Control,  Welder
WelderWelder,  Superintendent
InsulatorInsulator, CIVIL MODS
InsulatorIQC, 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

Not applicable
Author

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.

goldnejea8
Partner - Creator
Partner - Creator

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;

Jason_Michaelides
Luminary Alumni
Luminary Alumni

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

Not applicable
Author

I'll try it right now.

Not applicable
Author

Alright, It ALL make sense now.