Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
dmelillo
Contributor III

Create New Fields using File Name

I have many csv files I need to load with a standard naming convention ... examples are:

200704-jenkins

200705-jenkins

200706-jenkins

I would like to load all fields and created two more fields

PERIOD = first 5 characters of file name (i.e 200704)

SUBJECT = characters after hyphen ( - ) (i.e jenkins)

I also need to turn the PERIOD into a date that can be used by Qlikview for time series analysis.

Any help is appreciated.

Thank you,

- dave

1 Solution

Accepted Solutions
MVP
MVP

Re: Create New Fields using File Name

Yes.. Completely forgot that FileName() will give extension also.

You can replace FileName() with FileBaseName().. or you have to use two subfield for each line.. i.e. PERIOD and SUBJECT

9 Replies
MVP
MVP

Re: Create New Fields using File Name

Load

     *,

     SubField(FileName(),'-',1) as PERIOD,

     SubField(FielName(),'-',-1) as SUBJECT

From YourFileName;

Re: Create New Fields using File Name

I think it might be better to use FileBaseName() because FileName will include the file extension giving jenkins.csv instead of jenkins

MVP
MVP

Re: Create New Fields using File Name

Yes.. Completely forgot that FileName() will give extension also.

You can replace FileName() with FileBaseName().. or you have to use two subfield for each line.. i.e. PERIOD and SUBJECT

rahulpawarb
Valued Contributor III

Re: Create New Fields using File Name

Hello David,

I agree with the Manish! However, I have tweaked the definitions little bit to handle the file extensions (in this case .csv). In above example SUBJECT field will have file extension as well.

SampleData:

LOAD Day,

     Pageviews,

     [Unique Visitors],

     SubField(Replace(FileName(),.&FileExtension(),''),'-',1) as PERIOD,

     SubField(Replace(FileName(),.&FileExtension(),''),'-',-1) as SUBJECT

FROM

[201701-Analytics.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 6 lines);

Hope this will help.

Thank you!
Rahul

dmelillo
Contributor III

Re: Create New Fields using File Name

I can confirm that worked for creating PERIOD and SUBJECT. Will mark as correct.

Last part of the question though - How do I get from the PERIOD field I have now - i.e 200811 - to an actual date - i.e MM/DD/YYYY

dmelillo
Contributor III

Re: Create New Fields using File Name

Thank you for answering Rahul but when I used FileBaseName() i got no extension

Re: Create New Fields using File Name

May be like this:

Date(Date#(SubField(FileName(),'-',1), 'DDMMYY'), 'MM/DD/YYYY') as PERIOD

Assuming 200811 means 20th August 2011

rahulpawarb
Valued Contributor III

Re: Create New Fields using File Name

Hello David,

I was in the flow of drafting you a reply; meanwhile, Sunny T and Manisha reverted you (However, I have marked reply as Liked for both ;-) ).

Regards!
Rahul

dmelillo
Contributor III

Re: Create New Fields using File Name

Date(Date#(SubField(FileName(),'-',1), 'YYYYMM'), 'MM/DD/YYYY') as DATE is what I used. You were correct but I just had the PERIOD in YYYYMM format, not DDMMYY

Thank you as always Sunny

Community Browser