Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Connect 2026! Turn data into bold moves, April 13 -15: Learn More!
cancel
Showing results for 
Search instead for 
Did you mean: 
ERPReporter
Contributor
Contributor

Capturing Date from a FileName as a Table Value

Date is part of the filename containing data that I am loading.  The filename is:

Open Orders Report_20220801.csv

I would like to capture the date "20220801" and add it to the table data for each row.

Any examples would be appreciated.

Labels (4)
1 Solution

Accepted Solutions
deepanshuSh
Creator III
Creator III

Hi @ERPReporter You have to use a mixture of two functions one is filebasename() and another is the left/right function or subfield if there is specific pattern in the name of the file. 

 

I have attached some screenshots for the functionality and its usage. Hope it helps. 

 

T1:
LOAD
FileBaseName() as Filebase_name,
Country,
Institution,
"Year"
FROM [lib://DataFiles/Task_Lookup_2312.xlsx]
(ooxml, embedded labels, table is Sheet2);


NoConcatenate

Load *,
Right(Filebase_name, 4) as Filebase_Date
Resident T1;

Drop Table T1; 

Screenshot 2022-09-29 at 9.48.00 PM.png

 

You can do more things with the date to isolate actual dates and year from that using more analysis and similar left/right functions. 

 

 

 

 

Trial and error is the key to get unexpected results.

View solution in original post

2 Replies
deepanshuSh
Creator III
Creator III

Hi @ERPReporter You have to use a mixture of two functions one is filebasename() and another is the left/right function or subfield if there is specific pattern in the name of the file. 

 

I have attached some screenshots for the functionality and its usage. Hope it helps. 

 

T1:
LOAD
FileBaseName() as Filebase_name,
Country,
Institution,
"Year"
FROM [lib://DataFiles/Task_Lookup_2312.xlsx]
(ooxml, embedded labels, table is Sheet2);


NoConcatenate

Load *,
Right(Filebase_name, 4) as Filebase_Date
Resident T1;

Drop Table T1; 

Screenshot 2022-09-29 at 9.48.00 PM.png

 

You can do more things with the date to isolate actual dates and year from that using more analysis and similar left/right functions. 

 

 

 

 

Trial and error is the key to get unexpected results.
ERPReporter
Contributor
Contributor
Author

Thanks.  That helps!