Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

String manipulation

Hello Community,

I have two string manipulation questions:

  1. at the moment my .csv files have the following format: 20141016#name.csv.
    Using the filename function how can I turn "20141016" into an actual date (feel free to recommend a better formatting of the date) and also get the name value (without the ".csv") to load both into separate columns?

  2. how do I split a filename (in this case it has nothing to do with the filename function) into name and extension when the filename contains more than one "."
      1. Example:
        Lower(Right(Name, Len(Name)-Index(Name, '.', 1))) AS Extension
        This works with a "normal" file name but what about:

                              "filename 1. of 2.txt"

                    So basically how do I go from the right to get the first "."?

Thank you.

1 Solution

Accepted Solutions
jonathandienst
Partner
Partner

Hi

1.     Date(Date#(Left(FileBaseName(), 8), 'YYYYMMDD'))

2a.     SubField(Name, '.', -1) As Extension

2b.     Left(Name, Len(Name) - Len(SubField(Name, '.', -1)) -1) As FileName

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

4 Replies
alexandros17
Partner
Partner

For the second question use filename() and fileextension() they shoul have to work ...

for the first question an option (apart date formatting) could be:

Makedate(left(filename,4), mid(filename,5,2), mid(filename,7,2))
LEt me know
martinpohl
Partner
Partner

look at filebasename and fileextension

Fileinformation:

LOAD

     date(left(filebasename(),6),'DD.MM.YYYY') as Date,

     FileExtension() as Extension

FROM

*.qvd

(qvd);

Regards

jonathandienst
Partner
Partner

Hi

1.     Date(Date#(Left(FileBaseName(), 8), 'YYYYMMDD'))

2a.     SubField(Name, '.', -1) As Extension

2b.     Left(Name, Len(Name) - Len(SubField(Name, '.', -1)) -1) As FileName

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

Not applicable
Author

Thank you all for showing different ways.