topic Re: Creating Field Based on String of Another Field in QlikView

Creating Field Based on String of Another Field

Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Creating Field Based on String of Another Field

oknotsen — Sun, 20 Mar 2016 09:43:26 GMT

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.

Re: Creating Field Based on String of Another Field

Sun, 20 Mar 2016 11:44:01 GMT

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?

Re: Creating Field Based on String of Another Field

oknotsen — Sun, 20 Mar 2016 15:33:48 GMT

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

Re: Creating Field Based on String of Another Field

MarcoWedel — Sun, 20 Mar 2016 17:31:09 GMT

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

Re: Creating Field Based on String of Another Field

Saravanan_Desingh — Tue, 22 Mar 2016 00:54:48 GMT

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.

Re: Creating Field Based on String of Another Field

MarcoWedel — Wed, 23 Mar 2016 00:54:55 GMT

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

Re: Creating Field Based on String of Another Field

Saravanan_Desingh — Wed, 23 Mar 2016 01:10:57 GMT

Thanks for the explanation, Marco. Nice trick.

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

Re: Creating Field Based on String of Another Field

MarcoWedel — Wed, 23 Mar 2016 01:17:13 GMT

too much honour ...