Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

SubField Function in qlikview

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

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

];


Capture.PNG


PrashantSangle

Hi,

or may be

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

Regards,

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

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

Not applicable
Author

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
Specialist II
Specialist II

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

Not applicable
Author

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

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

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        

Not applicable
Author

I tried, but dint work

Not applicable
Author

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.