Creating Field Based on String of Another Field

[#1]

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?

[oknotsen]

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.

[#3]

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]

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).

[MarcoWedel]

Hi,

one solution could be:

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 ...