Extract string using Subfield

[karan_kn]

Need to extract text from 3rd node using subfield.

Title_Name	Title_Name_1	Expecting Result
AB_SDF_87544515_FGDFP_ER	87544515	87544515
DG_JTY_74617678-C_EAEM3eADMA4711	Notfound	74617678
GHF_HFE_76464369_ASAS-ASD	76464369	76464369
LFGDFJM_DFGDFG_GFGGF_DFSDF_DF	Notfound	Notfound
PFS_KUD_83851133-KSD_DFVD_SAD	Notfound	83851133
sdfd_DFGDFGDFG	Notfound	Notfound
SDFWE_WERWE_80157751HK-IN-A1-N1-ASG4702	Notfound	80157751
SDFWE_WERWE_82004442AA	Notfound	82004442
SE_JHGFDF_85697824_OUTP_DFG	85697824	85697824
TRYR_SF_78311060-A_Tech-FAR-SSD	Notfound	78311060

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;

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

[m_woolf]

Try this:

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

[sunny_talwar]

Or this

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

[Vegar · Accepted solution]

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);

[karan_kn]

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

[Vegar]

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.

[karan_kn]

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

[Vegar]

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)

[rajaxavier]

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;