Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted

Re: SubField Function in qlikview

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

];


Capture.PNG


Highlighted

Re: SubField Function in qlikview

Hi,

or may be

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

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Highlighted

Re: SubField Function in qlikview

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

Highlighted
Not applicable

Re: SubField Function in qlikview

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

Highlighted
Specialist II
Specialist II

Re: SubField Function in qlikview

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

Highlighted
Not applicable

Re: SubField Function in qlikview

Nope doesn't work

Highlighted

Re: SubField Function in qlikview

Hi,

did you try my suggestion??

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

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Highlighted
Specialist II
Specialist II

Re: SubField Function in qlikview

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        

Highlighted
Not applicable

Re: SubField Function in qlikview

I tried, but dint work

Highlighted
Not applicable

Re: SubField Function in qlikview

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.