topic Re: Date in QlikView

Date

kevbrown — Tue, 10 May 2016 09:51:23 GMT

Hi,

I'm bringing in a series of Excel Spreadsheets into a Qlikview document but the spreadsheets don't have a date column. I would like to add a date column but it needs to reflect the name of each workbook. So, I have files called - "Info WC 01-04-16", "Info WC 08-04-16", "Info WC 15-04-16" and so on. It's the WC date I need to create. An added complication is that I concatenate each new file on a weekly basis, so I'm just looking for the newest file in the folder and adding it the bottom of the current data.

Make sense?

Kev

Re: Date

MK_QSL — Tue, 10 May 2016 09:53:31 GMT

Adding file name to qlikview | Qlik Community‌

how to Load only the latest files?

Load latest Excel file to Qlikview

Re: Date

sunny_talwar — Tue, 10 May 2016 09:54:13 GMT

Try this in your load statement

LOAD AllFieldNames,

Date(Date#(Right(BaseFileName(), 8), 'DD-MM-YY')) as Date

FROM SourceFile;

Re: Date

Kushal_Chawda — Tue, 10 May 2016 09:57:14 GMT

see this

Load only new or updated excel files dynamically

Re: Date

kevbrown — Tue, 10 May 2016 10:02:40 GMT

Thanks but it states Bad field name BaseFIleName. Is that because it should be my file name in there?

Re: Date

kevbrown — Tue, 10 May 2016 10:16:06 GMT

Thanks,

Filename is now in my date but can i trim it to only include the date part. Currently it states Info WC 15-04-16.xls

Re: Date

sunny_talwar — Tue, 10 May 2016 10:41:53 GMT

BaseFileName() is a function which picks the information from the the file name from which you are loading the data.

Re: Date

MK_QSL — Tue, 10 May 2016 10:45:24 GMT

Use same function provided by Sunny T.

Re: Date

kevbrown — Tue, 10 May 2016 10:55:11 GMT

IT's not liking the Brackets after BaseFileName

Re: Date

sunny_talwar — Tue, 10 May 2016 10:57:41 GMT

Sorry the function is FileBaseName()

FileBaseName ‒ QlikView‌

LOAD AllFieldNames,

Date(Date#(Right(FileBaseName(), 8), 'DD-MM-YY')) as Date

FROM SourceFile;

Re: Date

kevbrown — Tue, 10 May 2016 10:58:12 GMT

Should be FileBaseName

Re: Date

sunny_talwar — Tue, 10 May 2016 10:58:46 GMT

Yes, I just found my mistake. Apologize for that

Re: Date

kevbrown — Tue, 10 May 2016 11:00:48 GMT

no worries and thanks for your help