topic Re: Extract string using Subfield in QlikView

Extract string using Subfield

karan_kn — Wed, 25 Nov 2020 16:16:04 GMT

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;

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

Re: Extract string using Subfield

m_woolf — Tue, 28 Jan 2020 18:33:29 GMT

Try this:

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

Re: Extract string using Subfield

sunny_talwar — Tue, 28 Jan 2020 19:05:38 GMT

Or this

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

Re: Extract string using Subfield

Vegar — Tue, 28 Jan 2020 20:39:59 GMT

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

Re: Extract string using Subfield

karan_kn — Wed, 29 Jan 2020 07:55:14 GMT

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

Re: Extract string using Subfield

Vegar — Wed, 29 Jan 2020 09:02:18 GMT

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.

Re: Extract string using Subfield

karan_kn — Wed, 29 Jan 2020 09:21:22 GMT

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

Re: Extract string using Subfield

Vegar — Wed, 29 Jan 2020 10:25:44 GMT

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)

Re: Extract string using Subfield

rajaxavier — Sun, 26 Apr 2020 13:55:19 GMT

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;