Converting filename from 2015Jul to 2015Q3M1

[#1]

Hi ,

I need to convert my filenames from following format: 2015Jul, 2015Aug, 2015Sep

To 2015Q3M1, 2015Q3M2, 2015Q3M3 at the script level.

Can anyone help me with a sample solution which I can implement in my larger solution.

Note: I have multiple excels and they keep coming in every month.

A sample screenshot has been attached.

Thanks for all the help.

Regards,

Sachin

[sunny_talwar · Accepted solution]

May be this:

Data:

CrossTable(MONTH,[Forecast Values], 3)

LOAD Metric,

      Dual(Year(Date#(FileBaseName(),'YYYYMMM')) & 'Q' & Ceil(Month(Date#(FileBaseName(),'YYYYMMM'))/3) & 'M' &

      (If(Mod(Month(Date#(FileBaseName(),'YYYYMMM')), 3) = 0, 3, Mod(Month(Date#(FileBaseName(),'YYYYMMM')), 3))),

      Date#(FileBaseName(),'YYYYMMM')) as FileName,

     Country,

     [42005],

     [42036],

     [42064],

     [42095],

     [42125],

     [42156],

     [42186],

     [42217],

     [42248],

     [42278],

     [42309],

     [42339]

FROM

[2015*.xlsx]

(ooxml, embedded labels, table is Data);

New:

noconcatenate

LOAD *,

Year(trim(MONTH)) as Year,

month(trim(MONTH)) as Month,

monthname(trim(MONTH)) as MonthYear

Resident Data;

DROP Field MONTH;

DROP Table Data;

Data:

CrossTable(MONTH,[Forecast Values], 3)

LOAD Metric,

     Dual(Year(Date#(FileBaseName(),'YYYYMMM')) & 'Q' & Ceil(Month(Date#(FileBaseName(),'YYYYMMM'))/3) & 'M' & (Mod(Month(Date#(FileBaseName(),'YYYYMMM')), 3) + 1),

      Date#(FileBaseName(),'YYYYMMM')) as FileName,

    Country,

    [42005],

    [42036],

    [42064],

    [42095],

    [42125],

    [42156],

    [42186],

    [42217],

    [42248],

    [42278],

    [42309],

    [42339]

FROM

[2015*.xlsx]

(ooxml, embedded labels, table is Data);

New:

noconcatenate

LOAD *

,

Year(trim(MONTH)) as Year,

month(trim(MONTH)) as Month,

monthname(trim(MONTH)) as MonthYear

Resident Data;

DROP Field MONTH;

DROP Table Data;

[#3]

Thanks Sunny.. Appreciate your help..