Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Creating Field Based on String of Another Field

I would to create a new field in my load script that is based what string is found in another field. I already have a field with values similar to the ones below"

Field1:

"Sample: Oth Mtg Lns ($000)"

"Sample2: RE and Oth ($000)"

I need to create a second field called Type, which depends on what subtring is found in Field1. For instance:

"Other Mortgage Loans"  if Field1 contains Oth Mtg Lns

"Real Estate and Other" if Field1 contains RE and Oth

What is the best way to go about this?

8 Replies
oknotsen
Master III
Master III

Make a mapping table in which you add the strings found in Field1 in the first column and the value that needs to end up in Field2 as second column.

Now make an applyMap referring to this helper table.

Example of the mapping table:

Field1ConversionTable:
Mapping
LOAD * INLINE [
F1, F2
Oth Mtg Lns, Other Mortgage Loans
RE and Oth, Real Estate and Other
]
;

Example of the applyMap function:

applymap('Field1ConversionTable', mid(Field1, 8, 11), 'Unknown') as Field2

The actual code for the second value in the applyMap function depends on how the content of Field1 actually looks. You might need to play a bit with trim() and other string functions to find the position of the string you need to match against your mapping table.

May you live in interesting times!
Not applicable
Author

Thanks - this is helpful. What should I do if I want to look for my string anywhere within the field? Is there a find function or something like that that searches the entire field for a string?

oknotsen
Master III
Master III

You need to be able to recognize the string in some way. If you have some set structure / definition, I am sure a function can be found to extract that / make the match.

Btw,

If you consider a post helpful, you can flag it at such (via actions).

May you live in interesting times!
MarcoWedel

Hi,

one solution could be:

QlikCommunity_Thread_210150_Pic1.JPG

LET vSubStrDel = Hash128(Rand());  //substring delimiter unlikely to occur in source data

mapType:

Mapping

LOAD in,

    '$(vSubStrDel)'&out&'$(vSubStrDel)'

Inline [

in, out

Oth Mtg Lns,Other Mortgage Loans

RE and Oth,Real Estate and Other

];

table1:

LOAD *,

    TextBetween(MapSubString('mapType',Field1),'$(vSubStrDel)','$(vSubStrDel)') as Type

Inline [

Field1

Sample: Oth Mtg Lns ($000)

Sample2: RE and Oth ($000)

];

hope this helps

regards

Marco

Saravanan_Desingh

Hi Marco,

You are concatenating the out with a Rand and then taking the text in between it.

If I understand, you are doing this to use MapSubString. Am I right?

If not, can you please explain you code?

Is there any hidden advantage using Hash128? Thank you in advance.

MarcoWedel

Hi,

I'm marking the target string with some random delimiter string to be able to extract it with the TextBetween() function.

Using a Hash function seemed to be some way to generate a delimiter string that is highly unlikely to occur in any source string.

Second thought, just using

vSubStrDel = 'dh/58d$§&(1=4F24,w:f-4';

should have been random enough

Saravanan_Desingh

Thanks for the explanation, Marco. Nice trick.

I have created a separate folder for your Apps. Each one is gem.

MarcoWedel

too much honour ...