Month extract from Date

[bhavvibudagam]

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",

    A,

    G,

     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.

[sunny_talwar · Accepted solution]

May be try this

GlobalITAssesment:

LOAD

    "OR",

    "R",

    A,

    G,

     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);

[vinieme12]

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

];

[sunny_talwar]

May be this

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

[Clever_Anjos]

Please try with

GlobalITAssesment:

Load

     *,

     Year(Date) as Year,

     MonthName(Date) as MonthName,

     Month(Date) as Month;

LOAD

    "OR",

    "R",

    A,

    G,

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

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

(ooxml, embedded labels, table is Sheet1);

[sunny_talwar]

Use this

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