topic Re: Extract Date from File Name string in QlikView

Extract Date from File Name string

arpita — Thu, 03 Dec 2020 11:08:35 GMT

1st Question:

I have month wise files with name Trades extract for Trade Activity Reports20201027123456789. I used mid(FileName(),46,2)&'/'&mid(FileName(),42,4) as Year_Month and it gives me the month year value perfectly as 10/2020.

But this report is actually of previous month which means, data extracted in Oct is for the month of September. Is there a way to that and also, I want it show as Oct 2020 instead of 10/2020

2nd Question:

My QV has last 3 months data but in few objects, I want only Current month Data. How do I do it

Re: Extract Date from File Name string

edwin — Thu, 03 Dec 2020 13:37:41 GMT

1st question: your file name returns 10/2020 but you want it to return prior month - use the addmonths function and use -1 as the offset. this is assuming you already have extracted the date field
=addmonths(DATEFIELD,-1)

2nd question is formatting use date(DATEFIELD,'MMM YYYY')

3rd wuestion - first how do you define CURRENT month. you need to be careful here as it is possible your definition might fail at boundary conditions (1st of month especially). if your data is always 1 day behind you may want to test against today()-1
MONTHSTART(DATEFIELD) = MONTHSTART(today()-1)

Re: Extract Date from File Name string

arpita — Thu, 03 Dec 2020 13:48:29 GMT

Hi Edwin,

the addsmonth function in Script returns an error

For the current month scenerio, every month one file is generated with its previous month data. likewise, the QV while it loads, will load all the existing files in the folder. Now I want few objects to only reflect the latest month data only

Re: Extract Date from File Name string

edwin — Thu, 03 Dec 2020 16:06:36 GMT

the error in addmonths may be due to the field not being a date field. can you share the expression that created the date field? or are you just getting the year month per above? if so:

date(addmonths(date(mid(FileName(),46,2)&'/01/'&mid(FileName(),42,4) ,'MM/DD/YYYY'),-1),'MMM YYYY') will get you prior month in MMM YYYY format

if you are loading different months from the data file, you can add a flag in your calendar to flag the current month and use that in your chart or text objects.

Re: Extract Date from File Name string

MayilVahanan — Fri, 04 Dec 2020 02:54:32 GMT

Hi @arpita

Try like below

=Date(MakeDate(mid('Trades extract for Trade Activity Reports20201027123456789',42,4),mid('Trades extract for Trade Activity Reports20201027123456789',46,2)),'MMM YYYY')

gives Oct 2020

=Date(AddMonths(MakeDate(mid('Trades extract for Trade Activity Reports20201027123456789',42,4),mid('Trades extract for Trade Activity Reports20201027123456789',46,2)),-1),'MMM YYYY') gives Sep 2020

for 2nd, you can create a master calendar and set current month flag and use it.

Re: Extract Date from File Name string

arpita — Mon, 14 Dec 2020 17:51:53 GMT

Hi Edwin, This gives me only Dec 2019 for 2 of my files...wich should in actual be Oct 2020 and Nov 2020