topic Re: Date issue in QlikView

Date issue

bobbydave — Wed, 25 Nov 2020 16:16:04 GMT

XXXXXXXXX_MSL v0.169 Apr16.xlsx

XXXXXXXXX_MSL v0.169 May16.xlsx

XXXXXXXXX_MSL v0.169 June16.xlsx

XXXXXXXXX_MSL v0.169 July16.xlsx

XXXXXXXXX_MSL v0.169 Aug16.xlsx

I want to get the date from this file.

(Subfield(SubField(filename(), ' ', 3), '.', 1) as Date

Result:

Apr16

May16

June16

July16

Aug16

Which is fine until I change it into the date format.

Date below converted wont pick up July and June as they are in MMMM and not MMM format like Apr May and Aug

Date(Date#(Subfield(SubField(filename(), ' ', 3), '.', 1), 'MMMYY'), 'MMM YYYY') as Date

Apr 2016

May 2016

Aug 2016

Any ideas how I can get all months?

Re: Date issue

sunny_talwar — Thu, 25 Aug 2016 14:52:33 GMT

How about this:

Date(MonthStart(Alt(Num(Date#(Left(SubField(FileBaseName(), ' ', -1), 'MMMYY')), Num(Date#(Left(SubField(FileBaseName(), ' ', -1), 'MMMMYY')))), 'MMM YYYY') as Date

Date(MonthStart(Alt(Num(Date#(SubField(FileBaseName(), ' ', -1), 'MMMYY')), Num(Date#(SubField(FileBaseName(), ' ', -1), 'MMMMYY')))), 'MMM YYYY') as Date

Re: Date issue

sunny_talwar — Thu, 25 Aug 2016 14:56:03 GMT

Here is a sample for you to play around with

LOAD *,

Date(MonthStart(Alt(Num(Date#(SubField(FileBaseName, ' ', -1), 'MMMYY')), Num(Date#(SubField(FileBaseName, ' ', -1), 'MMMMYY')))), 'MMM YYYY') as Date;

LOAD * Inline [

FileBaseName

XXXXXXXXX_MSL v0.169 Apr16

XXXXXXXXX_MSL v0.169 May16

XXXXXXXXX_MSL v0.169 June16

XXXXXXXXX_MSL v0.169 July16

XXXXXXXXX_MSL v0.169 Aug16

];

Re: Date issue

sunny_talwar — Thu, 25 Aug 2016 14:58:18 GMT

In case you want to stick with FileName() function, rather than FileBaseName(), you can try this:

Date(MonthStart(Alt(Num(Date#(SubField(SubField(FileName(), ' ', -1), '.', 1), 'MMMYY')),

Num(Date#(SubField(SubField(FileName(), ' ', -1), '.', 1), 'MMMMYY')))), 'MMM YYYY') as Date

Sample:

LOAD *,

Date(MonthStart(Alt(Num(Date#(SubField(SubField(FileName, ' ', -1), '.', 1), 'MMMYY')),

Num(Date#(SubField(SubField(FileName, ' ', -1), '.', 1), 'MMMMYY')))), 'MMM YYYY') as Date;

LOAD * Inline [

FileName

XXXXXXXXX_MSL v0.169 Apr16.xlsx

XXXXXXXXX_MSL v0.169 May16.xlsx

XXXXXXXXX_MSL v0.169 June16.xlsx

XXXXXXXXX_MSL v0.169 July16.xlsx

XXXXXXXXX_MSL v0.169 Aug16.xlsx

];