Convert FW number in string form to date format

[gizzel123]

Hi All,

I have a column with Fiscal weeks in the from of string  as in"FW39_2012"

and I need month and quarter from it.

Could you please help.

Regards,

Shruti

[Colin-Albert]

It will depend when your fiscal year starts, but you could use something like

     makedate(2012) + (39 * 7)

     MONTH( makedate(2012) + (39 * 7))

     'Q' & CEIL( MONTH( makedate(2012) + (39 * 7)) / 12) AS qtr

you would need to add an offset for the start of your fiscal year.

[tresesco]

Try like:

Load

     Month(Date) as Month,

     'Q'&Ceil(Month(Date)/3) as Quarter;            //Preceding load

Load

          MakeWeekdate(SubField([Fiscal Week], '_',2), PurgeChar(SubField([Fiscal Week], '_',1),'FW')) as Date

From <>;

[Colin-Albert]

The Qtr should be divided by 3 not 12!

  'Q' & CEIL( MONTH( makedate(2012) + (39 * 7)) / 3) AS qtr

[MarcoWedel · Accepted solution]

=MakeWeekDate(SubField('FW39_2012','_',2), TextBetween('FW39_2012','FW','_'))

[MarcoWedel]

Month(MakeWeekDate(SubField('FW39_2012','_',2), TextBetween('FW39_2012','FW','_'))) as Month

'Q'&Ceil(Month(MakeWeekDate(SubField('FW39_2012','_',2), TextBetween('FW39_2012','FW','_')))/3) as Quarter

[gizzel123]

Thanks All.

That worked like magic.

[MarcoWedel]

You're welcome

Regards

Marco