Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a year field which holds the value '2011'
Is there a way I can change this to 2010-2011 so it reflects the financial year?
Currently I have: yearname(YEAR, 0, 4) as FinancialYear
This is giving me the value '1905-1906' for some reason.
I see,
these values probably don't match your standard DateFormat set in the script or OS.
You could read this in as
...
Date(Date#(FIELD,'YYYYMMDD') as YourDateField,
...
Hope this helps,
Stefan
brianm10,
if your financial year starts in April, you will need to give at least month information, too, otherwise QV can't tell if year 2011 belongs to 2010-2011 or 2011-2012, right?
You need to use something like
yearname(DATE,0,4)
Please not the use of a DATE with its numerical representation here, instead of YEAR.
Do you have a DATE field available, too?
Hope this helps,
Stefan
Thanks for the reply.
I have a month field but its in the format as 201110.
Will I need to change this also?
When I try and convert this field with the following code:
date(posting_date, 'MM-YYYY') as Date
This gives me the values:
Dec-1899
Apr-56962
Apr-56592
Apr-56592
posting_date is this YearMonth field you just mentioned? It seems it has no underlying numerical date representation, how is posting_date created? Read from a database or have you used a QV function on a DATE field?
Well, if posting_date is just a YearMonth value, you could create a DATE field based on that, e.g. in the script like:
LOAD
...
makedate(right(posting_date,4),left(posting_date,2),1) as MonthStartDate,
...
FROM ...
and then use this MonthStartDate as input to yearname, maybe also in the script
LOAD
...
makedate(right(posting_date,4),left(posting_date,2),1) as MonthStartDate,
yearname(makedate(right(posting_date,4),left(posting_date,2),1),0,4) as YearName,
...
FROM ...
Hope this helps,
Stefan
Its read from a flat file, basically its just test data.
Currently the only values in this field are '20111101', '20111102', '20111103'
So its a concatenation of year/month/day
Thanks for your help
I see,
these values probably don't match your standard DateFormat set in the script or OS.
You could read this in as
...
Date(Date#(FIELD,'YYYYMMDD') as YourDateField,
...
Hope this helps,
Stefan
Thanks, that peice of script fixed the issue