Create New Fields using File Name

[Anonymous]

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

[MK_QSL]

Load

     *,

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

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

From YourFileName;

[sunny_talwar]

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

[MK_QSL · Accepted solution]

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]

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

[Anonymous]

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

[Anonymous]

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

[sunny_talwar]

May be like this:

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

Assuming 200811 means 20th August 2011

[rahulpawarb]

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

[Anonymous]

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