topic Re: Convert FW number in string form to date format in QlikView

Convert FW number in string form to date format

gizzel123 — Mon, 27 Oct 2014 09:55:38 GMT

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 — Mon, 27 Oct 2014 09:59:35 GMT

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.

tresB — Mon, 27 Oct 2014 10:02:32 GMT

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 — Mon, 27 Oct 2014 10:03:08 GMT

The Qtr should be divided by 3 not 12!

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

MarcoWedel — Mon, 27 Oct 2014 10:07:43 GMT

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

MarcoWedel — Mon, 27 Oct 2014 10:09:20 GMT

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 — Tue, 28 Oct 2014 06:08:11 GMT

Thanks All.

That worked like magic.

MarcoWedel — Tue, 28 Oct 2014 07:30:00 GMT

You're welcome

Regards

Marco