Question on Year(Date)

[Anonymous]

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

[danieloberbilli]

seems that there are values in your field 'Value' without a Date (or without a proper Date according to the calendar)

[Anonymous]

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

[danieloberbilli]

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

...

[danieloberbilli]

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

[Anonymous]

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

[danieloberbilli]

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

[danieloberbilli]

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

[danieloberbilli · Accepted solution]

[PrashantSangle]

Hi,

Handle this issue in front end

Use

Year(SDate) as calculated Dimension

Regards