Month and Year

[#1]

Hi,

I have a file name as such 'DetailPL_FY1415.xlsx'. The file contains two fields in particular labelled at FY and Month.

FY contains the value 2014/15

Month contains the values Jan, Feb, March etc

The financial year starts in August and ends in September.

How do I create a MonthYear field? I,e Dec-2014, Jan-2015 etc...? Can I still use the filename?

Thanks

[anbu1984]

LOAD *,Month & '-' & Subfield(FY,'/',1) As  MonthYear Inline [

FY,Month

2014/15,Jan

2014/15,Feb ];

[#3]

Month & '-' & LEFT(FY,4) AS MonthYear

[its_anandrjs]

Hi,

You can try

Load Month&'-'&Left(FY,4) as MonthYear;

Load * Inline

[

FY,Month

2014/15,Sep

2014/15,Oct

2014/15,Nov

2014/15,Dec

];

Or

Month&'-'& Year( Date(Left(FY,4),'YYYY) ) AS MonthYear;

Regards

Anand

[#5]

Hi Anbu

That works fine, but again, the issue is Aug, Sep, Oct, Nov, Dec should be 2014 and Jan, Feb, Mar till Jul need to be 2015.

How do I make this split?

[anbu1984]

LOAD *,Month & '-' & If(Match(Month,'Aug','Sep','Oct','Nov','Dec'),Subfield(FY,'/',1),'20'&Subfield(FY,'/',2)) As  MonthYear Inline [

FY,Month

2014/15,Jan

2014/15,Feb

2014/15,Mar

2014/15,Apr

2014/15,May

2014/15,Jun

2014/15,Jul

2014/15,Aug

2014/15,Sep

2014/15,Oct

2014/15,Nov

2014/15,Dec

];

[#7]

Thank you all