How to get quarter year month from the file name

[HRITHIKROSHAN888]

Mine filenames are as follows

KPI%20IIPF%20MAY%202020%20POZNAN%20data

KPI%20IIPF%20APR%202019%20POZNAN%20data

KPI%20IIPF%20SEP%202018%20POZNAN%20data

KPI%20IIPF%20SEP%202020%20POZNAN%20data

i want to get year month quarter from the file name when i load these files

i wrote a loop where it will pull the data from these files along with using FILENAME() AS FILENAME as a Field(here i want new fields as year month quarter based upon the filename )

when i use mid(filename() , 14) i am getting from MAY%202020%20POZNAN%20data  i want to show only  MAY 2020 please help

[Kushal_Chawda · Accepted solution]

If your file name format is consistent then try below

date(date#(mid(filebasename(),14,3)&mid(filebasename(),20,4),'MMMYYYY'),'MMM YYYY') as Month,

year(date#(mid(filebasename(),14,3)&mid(filebasename(),20,4),'MMMYYYY')) as Year,

'Q'&ceil(month(date#(mid(filebasename(),14,3)&mid(filebasename(),20,4),'MMMYYYY'))/3)&'-'&
year(date#(mid(filebasename(),14,3)&mid(filebasename(),20,4),'MMMYYYY'))as QuarterYear

[HRITHIKROSHAN888]

For KPI%20IIPF%20MAY%202020%20POZNAN%20data i want output as Q2 2020

For KPI%20IIPF%20SEP%202018%20POZNAN%20data i want output as Q3 2018

For KPI%20IIPF%20SEP%202020%20POZNAN%20data i want output as Q3 2020

For KPI%20IIPF%20APR%202019%20POZNAN%20data i want output as Q2 2019

[Kushal_Chawda · Accepted solution]

try below

date(date#(mid(filebasename(),14,3)&mid(filebasename(),20,4),'MMMYYYY'),'MMM YYYY') as Month,

year(date#(mid(filebasename(),14,3)&mid(filebasename(),20,4),'MMMYYYY')) as Year,

'Q'&ceil(month(date#(mid(filebasename(),14,3)&mid(filebasename(),20,4),'MMMYYYY'))/3)&' '&
year(date#(mid(filebasename(),14,3)&mid(filebasename(),20,4),'MMMYYYY'))as QuarterYear

[HRITHIKROSHAN888]

Thanks KUSH for your fast reply it is working perfectly thanks again