topic Re: String manipulation in QlikView

String manipulation

Thu, 16 Oct 2014 08:34:15 GMT

Hello Community,

I have two string manipulation questions:

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?
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 "."

"filename 1. of 2.txt"

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

Thank you.

alexandros17 — Thu, 16 Oct 2014 08:39:06 GMT

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 — Thu, 16 Oct 2014 08:39:59 GMT

look at filebasename and fileextension

Fileinformation:

LOAD

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

FileExtension() as Extension

FROM

*.qvd

(qvd);

Regards

jonathandienst — Thu, 16 Oct 2014 08:43:47 GMT

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

Thu, 16 Oct 2014 11:57:51 GMT

Thank you all for showing different ways.