Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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_michaelid
Honored Contributor II

Re: Make a new field based on string content of other field

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

11 Replies
jason_michaelid
Honored Contributor II

Make a new field based on string content of other field

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

Make a new field based on string content of other field

Sure one second.

goldnejea8
Contributor

Make a new field based on string content of other field

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

Re: Make a new field based on string content of other field

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

Make a new field based on string content of other field

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
Contributor

Re: Make a new field based on string content of other 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;

jason_michaelid
Honored Contributor II

Re: Make a new field based on string content of other field

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

Make a new field based on string content of other field

I'll try it right now.

Not applicable

Make a new field based on string content of other field

Alright, It ALL make sense now.