topic Re: Create New Fields using File Name in App Development

Create New Fields using File Name

Anonymous — Fri, 13 Jan 2017 18:33:35 GMT

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

Re: Create New Fields using File Name

MK_QSL — Fri, 13 Jan 2017 18:42:41 GMT

Load

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

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

From YourFileName;

Re: Create New Fields using File Name

sunny_talwar — Fri, 13 Jan 2017 18:57:41 GMT

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

Re: Create New Fields using File Name

MK_QSL — Fri, 13 Jan 2017 18:59:40 GMT

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

Re: Create New Fields using File Name

rahulpawarb — Fri, 13 Jan 2017 19:14:11 GMT

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

Re: Create New Fields using File Name

Anonymous — Fri, 13 Jan 2017 19:18:00 GMT

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

Re: Create New Fields using File Name

Anonymous — Fri, 13 Jan 2017 19:19:02 GMT

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

Re: Create New Fields using File Name

sunny_talwar — Fri, 13 Jan 2017 19:21:56 GMT

May be like this:

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

Assuming 200811 means 20th August 2011

Re: Create New Fields using File Name

rahulpawarb — Fri, 13 Jan 2017 19:25:16 GMT

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

Re: Create New Fields using File Name

Anonymous — Fri, 13 Jan 2017 19:25:36 GMT

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