SubField Function in qlikview

[#1]

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

[sunny_talwar]

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"}]

];

[PrashantSangle]

Hi,

or may be

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

Regards,

[sunny_talwar]

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

[#5]

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

[ThornOfCrowns]

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_','')

[#7]

Nope doesn't work

[PrashantSangle]

Hi,

did you try my suggestion??

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

Regards

[preminqlik]

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        

[#10]

I tried, but dint work

[#11]

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.