Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
HRITHIKROSHAN888
Contributor
Contributor

How to get quarter year month from the file name

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

2 Solutions

Accepted Solutions
Kushal_Chawda

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

View solution in original post

Kushal_Chawda

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

View solution in original post

4 Replies
Kushal_Chawda

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
Contributor
Contributor
Author

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

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
Contributor
Contributor
Author

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