topic Re: Extract Date from File String in QlikView

Extract Date from File String

arpita — Mon, 08 Feb 2021 14:42:11 GMT

Can you please help me to extract the date from the File string

Interst_OpenExcp_Report_ABC_08_02_2021.

Also, I need to filter data in my table. ie. Data under SET_ID should not contain ABC anywhere in it. So for the below table, it should only show me only the highlighted ones

SET_ID

ABCABC_UNMATCH_730

ABCGFX_FX_909

MNOWSS_LNDEP_905

GFXGFX_CATABCLL_773

GFXGFX_FX_771

GFXGFX_FXS_772

GFXWSS_ARBI_901

SYXECOABC_CATCHALL_998

SYXECOABC_IRS_770

Re: Extract Date from File String

QFabian — Mon, 08 Feb 2021 20:37:58 GMT

Hi @arpita , i made this script, hope works for you. I attached the qvw and txt created for this exercise.

LOAD
makedate(subfield(FileBaseName(), '_', 7), subfield(FileBaseName(), '_', 6), subfield(FileBaseName(), '_', 5)) as Date,
SET_ID
FROM
[C:\Users\fquez\Documents\comm\Interst_OpenExcp_Report_ABC_08_02_2021.txt]
(txt, utf8, embedded labels, delimiter is '\t', msq)
Where
FindOneOf(SET_ID,'ABC')=0;

Re: Extract Date from File String

arpita — Wed, 10 Feb 2021 13:31:13 GMT

Hi @QFabian,

The date script did work....thank you 🙂

But for the Filter, I don't want the logic in my load script but for individual chart