Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm trying to use the calendar Master script to create the Year dimension for my data set.
However, i am not sure why some of the year appears as '-' (see Row 7).
Am i doing something wrong? Or do need to first format my date field upon loading?
Row | Year | sum(Value) |
Row 1 | 2009 | 279.49 |
Row 2 | 2010 | 0.00 |
Row 3 | 2011 | 191,826.79 |
Row 4 | 2012 | 8,593.27 |
Row 5 | 2013 | 101,875.40 |
Row 6 | 2014 | 2,015,958.22 |
Row 7 | - | 5,142,751.10 |
SDate is the date field I am using.
CalendarMaster:
LOAD
Date(SDate) AS SDate,
Year(SDate) AS Year,
'Q' & Ceil(Month(SDate) / 3) AS Quarter,
Month(SDate) As Month,
Day(SDate) As Day,
Week(SDate) As Week;
Load Date(MinDate + IterNo() -1 ) AS SDate While (MinDate + IterNo() - 1) <= Num(MaxDate);
Load
Min(SDate) AS MinDate,
Max(SDate) AS MaxDate
RESIDENT FactSales;
thanks
Derrell
seems that there are values in your field 'Value' without a Date (or without a proper Date according to the calendar)
Hi Daniel,
thanks for your reply.
This is some of the dates in the SDate field - looked like a valid date but it is not returning the Year
I think its because you first have to define SDate as a Date - then you can interpret the Date as a Year. So just move the line:
Year(SDate) as Year
to a preceeding load:
CalendarMaster:
LOAD *,
Year(SDate) as Year
;
Load
...
in your original load your SDate is interpreted as a Date field,
afterwards in the preceeding load the date field is interpreted to a year
the strange thing is that it works for some, but some it does not work.
in the preceeding load, i have defined SDate field as date => Date(SDate) as SDate
but it still yield the same result
no - don't define the date in the preceeding load - you have to move your Year()-line there.
So keep your date() in the normal load (this will be processed first in the scriptrun)
the year() line will be processed based on the date-interpretation from the normal load in a second step by the script run. Please see again the scripting example I gave you
Sorry Derrell - just realized that you are dealing already with several preceeding loads. So just put the one with the year() on top o the others and it should work fine
Hi,
Handle this issue in front end
Use
Year(SDate) as calculated Dimension
Regards