topic Re: Month extract from Date in QlikView

Month extract from Date

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

Hi Experts,

Can any one please help me on this.

I am trying to extract year,Month and Date from the File Name like below.

Script:

GlobalITAssesment:

LOAD

"OR",

"R",

makedate(mid(FileName(),34,4),mid(FileName(),38,2),mid(FileName(),40,2)) as Date,

monthname(makedate(mid(FileName(),34,4),(mid(FileName(),38,2)-1),mid(FileName(),40,2))) as MonthName,

(mid(FileName(),38,2)-1)&'/'&mid(FileName(),34,4) as Year_Month,

mid(FileName(),34,4) as Year,

mid(FileName(),38,2)-1 as Month,

mid(FileName(),40,2) as Day

FROM [lib://Source/*.xlsx]

(ooxml, embedded labels, table is Sheet1);

The above script is working fine in Oct and Nov Month files but coming to Jan Month file there is an issue.

When we are in January Month and Subtract -1 then its not finding December.

Please help me.

Thanks in advance.

Re: Month extract from Date

sunny_talwar — Thu, 11 Jan 2018 12:19:05 GMT

May be try this

GlobalITAssesment:

LOAD

"OR",

"R",

makedate(mid(FileName(),34,4),mid(FileName(),38,2),mid(FileName(),40,2)) as Date,

monthname(makedate(mid(FileName(),34,4),(mid(FileName(),38,2)-1),mid(FileName(),40,2))) as MonthName,

Date(MonthStart(makedate(mid(FileName(),34,4),mid(FileName(),38,2),mid(FileName(),40,2))), 'M/YYYY') as Year_Month,

mid(FileName(),34,4) as Year,

Num(Month(makedate(mid(FileName(),34,4),mid(FileName(),38,2),mid(FileName(),40,2)))) as Month,

mid(FileName(),40,2) as Day

FROM [lib://Source/*.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: Month extract from Date

vinieme12 — Thu, 11 Jan 2018 12:27:37 GMT

even simpler would be to use Textbetween() if your preceeding and trailing text is static

load MonthName(Date#(Trim(TextBetween(StringToDate,'Apps ',' V1')),'YYYYMMDD')) as MonthIS inline [

StringToDate

Global IT Assesment - Pilot Apps 20171029 V1 -Oct Metrics

Global IT Assesment - Pilot Apps 20171118 V1 -Nov Metrics

Global IT Assesment - Pilot Apps 20180109 V1 -Jan Metrics

];

Re: Month extract from Date

sunny_talwar — Thu, 11 Jan 2018 12:40:38 GMT

May be this

Date(MonthStart(makedate(mid(FileName(),34,4), mid(FileName(),38,2), mid(FileName(),40,2)), -1), 'MMM YYYY') as MonthName,

Re: Month extract from Date

Clever_Anjos — Thu, 11 Jan 2018 12:50:40 GMT

Please try with

GlobalITAssesment:

Load

Year(Date) as Year,

MonthName(Date) as MonthName,

Month(Date) as Month;

LOAD

"OR",

"R",

Date#(TextBetween(BaseFileName(),'Apps ', ' V'),'YYYYMMDD') as Date

FROM [lib://Source/*.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: Month extract from Date

sunny_talwar — Thu, 11 Jan 2018 12:58:28 GMT

Use this

Num(Month(MonthStart(makedate(mid(FileName(),34,4), mid(FileName(),38,2), mid(FileName(),40,2)), -1))) as Previous_Month,