Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Extract string using Subfield

Need to extract text from 3rd node using subfield.

Title_NameTitle_Name_1Expecting Result
AB_SDF_87544515_FGDFP_ER8754451587544515
DG_JTY_74617678-C_EAEM3eADMA4711Notfound74617678
GHF_HFE_76464369_ASAS-ASD7646436976464369
LFGDFJM_DFGDFG_GFGGF_DFSDF_DFNotfoundNotfound
PFS_KUD_83851133-KSD_DFVD_SADNotfound83851133
sdfd_DFGDFGDFGNotfoundNotfound
SDFWE_WERWE_80157751HK-IN-A1-N1-ASG4702Notfound80157751
SDFWE_WERWE_82004442AANotfound82004442
SE_JHGFDF_85697824_OUTP_DFG8569782485697824
TRYR_SF_78311060-A_Tech-FAR-SSDNotfound78311060

 

I'm using the following statement

******************
Script:

T0:
LOAD * INLINE [
Title_Name
AB_SDF_87544515_FGDFP_ER
SE_JHGFDF_85697824_OUTP_DFG
PFS_KUD_83851133-KSD_DFVD_SAD
SDFWE_WERWE_82004442AA
LFGDFJM_DFGDFG_GFGGF_DFSDF_DF
SDFWE_WERWE_80157751HK-IN-A1-N1-ASG4702
TRYR_SF_78311060-A_Tech-FAR-SSD
GHF_HFE_76464369_ASAS-ASD
DG_JTY_74617678-C_EAEM3eADMA4711
sdfd_DFGDFGDFG
];


NoConcatenate
LOAD Title_Name,
If(IsNum(SubField(Title_Name,'_',3)),SubField(Title_Name,'_',3),'Notfound') as Title_Name_1

Resident T0;
DROP Table T0;

*****************************

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

You don't need to use an IF to solve this problem. You can use the Alt() function as instead.

LOAD 
 Title_Name, 
 [Expecting Result],
 alt(num(SubField(keepchar(replace(Title_Name,'-','_'),'1234567890_'),'_',3)), 'Notfound') as CalcResult
FROM
   [https://community.qlik.com/t5/New-to-QlikView/Extract-string-using-Subfield/td-p/1670173]
   (html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

8 Replies
Highlighted
Master II
Master II

Try this:

If(IsNum(keepchar(SubField(Title_Name,'_',3),'0123456789')),keepchar(SubField(Title_Name,'_',3),'0123456789'),'Notfound') as Title_Name_1

Highlighted

Or this

If(IsNum(KeepChar(SubField(Replace(Title_Name, '-', '_'), '_', 3), '0123456789')), KeepChar(SubField(Replace(Title_Name, '-', '_'), '_', 3), '0123456789'), 'Notfound') as Title_Name_1
Highlighted
Partner
Partner

You don't need to use an IF to solve this problem. You can use the Alt() function as instead.

LOAD 
 Title_Name, 
 [Expecting Result],
 alt(num(SubField(keepchar(replace(Title_Name,'-','_'),'1234567890_'),'_',3)), 'Notfound') as CalcResult
FROM
   [https://community.qlik.com/t5/New-to-QlikView/Extract-string-using-Subfield/td-p/1670173]
   (html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

Highlighted
Creator II
Creator II

Thanks for the response, but the following scenario's not working, can you help me to fix? if hyphen before the 3rd node...

SD_DM_TEST-Value_654895237-charged-DE-A1-N1-TMA4514
SD_DM_TEST-Value_658795648-Simple-Confirmation-TRMA4751
SD_DM_TEST-SAe_785269845-STO-DE-A1-N1-RTMA3944
SD_DM_TEST-Value_658795428-Now-WW-MMA-Nov-Active-mail-check-AP-thinner-RSSA4575

Highlighted
Partner
Partner

In these examples the numbers are in the 4th subfield regardless of the hyphen or not.

In order to solve this you will need to find a logic ( set of rules ) that will identify the section you  are looking for, if the number section in these examples are to be the part of interrest then you will need to figure out hiw to identify these together with the logic of the earlier examples.

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
Creator II
Creator II

regardless 3rd or 4th subfield, if 'hyphen' comes before '_', the logic not working

Highlighted
Partner
Partner

Try to first fetch the third based on pure '_', then apply a second set of rules handling the '-' and the text in a second iteration. It might be easer due to less clutter in each expression row. 

LOAD
Subfield(replace(...ThirdSubfield)) as FinalValue
;

LOAD 
Subfield(text, '_', 3) as ThirdSubfield
FROM ...
;

(When you found the two step solution then you could consider merging everything into a single expression)

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
Contributor
Contributor

TAB:

LOAD * INLINE [
STRING
AB_SDF_87544515_FGDFP_ER
DG_JTY_74617678-C_EAEM3eADMA4711
GHF_HFE_76464369_ASAS-ASD
LFGDFJM_DFGDFG_GFGGF_DFSDF_DF
PFS_KUD_83851133-KSD_DFVD_SAD
sdfd_DFGDFGDFG
SDFWE_WERWE_80157751HK-IN-A1-N1-ASG4702
SDFWE_WERWE_82004442AA
SE_JHGFDF_85697824_OUTP_DFG
TRYR_SF_78311060-A_Tech-FAR-SSD

];


TAB1:
LOAD * ,
TEXT(IF(ISNUM(LEFT(SubField(STRING,'_',3),8)),LEFT(SubField(STRING,'_',3),8),'NOT FOUND')) AS OUTPUT
Resident TAB;

DROP Table TAB;