Add year to month field

[#1]

Hi All,

i have one data file, which contains Project Number, Project Name, November, December, January as fields. Here November and December belongs to 2013 year and January belongs to 2014. In future they may add data for February, March...

My requirement is i have to change November as Nov, December as Dec, January as Jan...... and i want add year as Nov-13, Dec-13, Jan-14, Feb-14...... But i don't have Year field in my datasheet

I tried with "left(Month,3)&'-'&right(if(left(Month,3) = 'Dec' or 'Nov' or left(Month,3) = 'Nov',  Year(Today()), if(left(Month,3) = 'Jan',Year(Today())+1)),2) as Month". But it is not correct way because it works for 3 months.

Please anybody can take a look into this requirement and give me suggestion.

i attached sample QVW and Data file also

Thanks,

M V

[#2]

Why don't you create a inline table and put this info and use it ?

regards,

Sree

[#3]

Hi,

     Let me clear two thing:

     1.  How can you say that particular month belongs to any year( like 2013 or 2014).

     2.  Is your date start from Nov-2013?

Regards,

Ashutosh

[#4]

Hi Asuthosh,

Actually my project starts from November (Current Month), Present i have data for 3 Months(November, December, January) in future it will extend.

Yes my date start from in November 2013

Please provide me some suggestion,thanks for reply

Thanks,

M V

[#5]

Hi Nair,

Thanks for reply, But I can't understand your suggestion.

could you Please attach sample file pls.

Thanks,

M V

[tresesco]

Try MonthName() function.

[#7]

Hi

Ans for your question is simple.

1.You want Nov,Dec etc instead of November, December etc

2. In case of year 13, 14 instead  of 2013, 2014

For this to work just open edit script there you can see some SET variables, If not you add following once then reload the script, it will do your job.

SET DateFormat='DD/MM/YY';

SET TimestampFormat='DD/MM/YY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

[markmccoid]

I'm not sure how sustainable the data source will be over multiple years, but within the constraints you have mentioned I think the attached will do the job.

I did flatten the Excel file using the crosstable function during the load.

I then created a mapping table that would map the months to numbers so that I could do the following:

Left(Month,3) & '-' & If(ApplyMap('MonthMap', Month)>10,'13','14') as MonthYear

Hopefully this gives you some ideas.

[#9]

Hi Kavitha,

Actually i don't have year field and i have only November, December, January fields.

Now i have to convert as Nov-13, Dec-13, Jan-13 , if data will extend like as November, December, January, February, March, April, May, June, July, August, September, October, November, December then the data will be as follows

Nov-13, Dec-13, Jan-14, Feb-14, Mar-14, Apr-14, May-14, Jun-14, Jul-14, Aug-14, Sep-14, Oct-14, Nov-14, Dec-14

Please have a look into this issue.

Thanks,

M V

[#10]

Hi All,

Actually i don't have year field and i have only November, December, January fields.

Now i have to convert as Nov-13, Dec-13, Jan-13 , if data will extend like as November, December, January, February, March, April, May, June, July, August, September, October, November, December then the data will be as follows

Nov-13, Dec-13, Jan-14, Feb-14, Mar-14, Apr-14, May-14, Jun-14, Jul-14, Aug-14, Sep-14, Oct-14, Nov-14, Dec-14

Please have a look into this issue. Please provide suggestion with example.

Thanks,

M V