topic Re: SubField Function in qlikview in QlikView

SubField Function in qlikview

Wed, 25 May 2016 08:43:40 GMT

Hi all,

From the following, I jus want LA_GRMTH as the output.

Is this possible using SubField function?

"RPRPLA_GRMTH_CD","relatedparty":"'

Thanks all for you help.

Vidhya

Re: SubField Function in qlikview

sunny_talwar — Wed, 25 May 2016 08:45:44 GMT

This just a single data point, what's the pattern here? share few more datapoints with the expected output to get a better response which will work across your database

Re: SubField Function in qlikview

Kushal_Chawda — Wed, 25 May 2016 08:51:51 GMT

Could be possible with Subfield but you need to provide few more data with expected output

Re: SubField Function in qlikview

swuehl — Wed, 25 May 2016 09:05:13 GMT

I agree that we need to come to a rule how the part of interest can be extracted from your input.

Looking at your sample, it would be difficult using Subfield(), but maybe using Textbetween():

=TextBetween('RPRPLA_GRMTH_CD','RPRP','_CD')

Re: SubField Function in qlikview

qlikview979 — Wed, 25 May 2016 09:52:15 GMT

Hi Vidhya,

try this,

Mid('RPRPLA_GRMTH_CD',5,8) as MID

If u share your data with required output. you will get good response From Community.

Regards,

Mahesh.

Re: SubField Function in qlikview

Wed, 25 May 2016 10:16:05 GMT

Thanks for the TextBetween function, but in my script, string is not constant and it will vary like the below.

Following are few examples

[{"relationship":"RPRPLA_GRMTH_CD","relatedparty":"LA_0"}]

[{"relationship":"RNMLA_WIFE_CD","relatedparty":"LA_0"}]

[{"relationship":"RNMLA_MOTH_CD","relatedparty":"LA_0"}]

[{"relationship":"RPRPLA_FATH_CD","relatedparty":"LA_0"}]

Output should be

LA_GRMTH

LA_WIFE

LA_MOTH

LA_FATH

Thanks in advance

Re: SubField Function in qlikview

chetansehgal — Wed, 25 May 2016 10:26:28 GMT

Hi Vidhya,

You can try below expression.

=index(Field_Name,$(vSearchString),1)

Field_Name is your field in the table

vSearchString is variable storing the string you want to search.

Can you please share the exact requirement as above function only returns the index of the search string in the main string.

Do you want to replace the string with some other string ?

Thanks,

Chetan

Re: SubField Function in qlikview

Wed, 25 May 2016 10:33:13 GMT

Thanks for the TextBetween function, but in my script, string is not constant and it will vary like the below.

Following are few examples

[{"relationship":"RPRPLA_GRMTH_CD","relatedparty":"LA_0"}]

[{"relationship":"RNMLA_WIFE_CD","relatedparty":"LA_0"}]

[{"relationship":"RNMLA_MOTH_CD","relatedparty":"LA_0"}]

[{"relationship":"RPRPLA_FATH_CD","relatedparty":"LA_0"}]

Output should be

LA_GRMTH

LA_WIFE

LA_MOTH

LA_FATH

Thanks in advance

Re: SubField Function in qlikview

sunny_talwar — Wed, 25 May 2016 10:49:08 GMT

How about this:

'LA_' & TextBetween(FieldName, 'LA_', '"') as NewField

Re: SubField Function in qlikview

swuehl — Wed, 25 May 2016 10:54:25 GMT

Or maybe

'LA_' & TextBetween(FieldName, 'LA_', '_CD') as NewField

Re: SubField Function in qlikview

sunny_talwar — Wed, 25 May 2016 10:56:02 GMT

Yup, this one works:

Table:

LOAD *,

'LA_' & TextBetween(FieldName, 'LA_', '_CD') as NewField;

LOAD * Inline [

FieldName

[{"relationship":"RPRPLA_GRMTH_CD","relatedparty":"LA_0"}]

[{"relationship":"RNMLA_WIFE_CD","relatedparty":"LA_0"}]

[{"relationship":"RNMLA_MOTH_CD","relatedparty":"LA_0"}]

[{"relationship":"RPRPLA_FATH_CD","relatedparty":"LA_0"}]

];

Re: SubField Function in qlikview

PrashantSangle — Wed, 25 May 2016 10:57:44 GMT

Hi,

or may be

='LA_' & SubField(fieldName,'_',2)

Regards,

Re: SubField Function in qlikview

sunny_talwar — Wed, 25 May 2016 11:00:00 GMT

But then this would include everything after LA_ until the next LA_

Re: SubField Function in qlikview

Wed, 25 May 2016 12:23:32 GMT

Thanks you for the explanation.

I have a field called Data. It is highly unstructured.

It will have strings like the below in it . I have pasted a portion of the strings.

":"LA_0","additionalinfo":{"exstngcustflg":"N","pepflg":"N","exstngclntid":"8268039","isdisabledflg":"N","smokerstatusflg":"N",

"ishdfcempflg":"N"},"employmentdetails":{[{"relationship":"RPRPLA_GRMTH_CD","relatedparty":"LA_0"}]

As you see, there are 2 'LA_' in this portion .

In the entire value, there are more than 3 LA_s

but I want to get only the relationship LA (LA_GRMTH).

'LA_' & TextBetween(FieldName, 'LA_', '_CD') will not work in this case.

Please let me know if I am clear

Thanks

Re: SubField Function in qlikview

ThornOfCrowns — Wed, 25 May 2016 12:46:10 GMT

Presuming your string has 'relationship' as the trigger for your LA_GRMTH / LA_MOTH / LA_FATH then use two text betweens:

='LA_'&
TextBetween(
TextBetween(

'":"LA_0","additionalinfo":"exstngcustflg":"N","pepflg":"N","exstngclntid":"8268039","isdisabledflg":"N","smokerstatusflg":"N","ishdfcempflg":"N"},"employmentdetails":{[{"relationship":"RPRPLA_GRMTH_CD","relatedparty":"LA_0"}]'
,'relationship','_CD')
,'LA_','')

Re: SubField Function in qlikview

Thu, 26 May 2016 05:49:11 GMT

Nope doesn't work

Re: SubField Function in qlikview

PrashantSangle — Thu, 26 May 2016 05:52:59 GMT

Hi,

did you try my suggestion??

can you provide us some more data. may be 10 to 15 records enough.

Regards

Re: SubField Function in qlikview

preminqlik — Thu, 26 May 2016 06:32:20 GMT

Hi Vidya, updated one

Try this

Load *,

mid(textbetween(SubField(FieldName,':',2),'"','"'),

findoneof(textbetween(SubField(FieldName,':',2),'"','"')

,'_',1)-2,

len(textbetween(SubField(FieldName,':',2),'"','"'))-(findoneof(textbetween(SubField(vFieldName,':',2),'"','"')

,'_',1)-2)

-2

)

as NewRequiredFieldName

From path;

Premhas

Re: SubField Function in qlikview

Thu, 26 May 2016 08:26:48 GMT

I tried, but dint work

Re: SubField Function in qlikview

Thu, 26 May 2016 08:34:42 GMT

Thanks all for your help, I still face an issue. Request you to help out

FORM_DATA is the field name.

I gave the following script

TextBetween(FORM_DATA,'"relationshipinfo":[{"relationship":"R','_CD')as NEWFIELD.

This seems to work fine except for cases where there are two relationships like the below (Wife and MOther)

"relationshipinfo":[{"relationship":"RNMLA_WIFE_CD","relatedparty":"LA_0"}],"

relationshipinfo":[{"relationship":"RNMLA_MOTH_CD","relatedparty":"LA_0"}],"

I get these two relationships Wife & mother at different position. My script is picking only the first one, NMLA_WIFE, whereas I should get NMLA_MOTH as well in this instance.

Thanks in advance.