Autogenerating dates within a fact table

[#1]

Hi All

I have a fact table with four fields.

Area

District

Version

Design ID

I would like to add a date field to the fact table, with the result being one row per date, per combination of fact fields.

The purpose is to then add other data items to create a historical footprint, allowing for further analysis to be performed. the dates will start on 1/1/2013 and continue until today().

Any help would be greatly appreciated

many thanks'

Rick

[amit_saini]

Rick,

Try this : (Suppose name of your date field is DateID)

Let StartCalendar = num(MakeDate(year(today())-1, '01', '01'));

Let EndCalendar = num(YearEnd(today()));

//---------------------------------------------------------------------

//Autogenerate Calendar with start and end Date

Cal:

LOAD Date ($(StartCalendar) + RecNo()-1) as CalDate

AutoGenerate ((today()-1) - $(StartCalendar)+1 );

//Left Join with no keys

left Join (Cal)

Load date(CalDate, 'DD-MMM-YYYY') as DateID

Resident Cal;

CAL:

Load

    DateID,

//    Date(SALES_DATE, 'DD/MM/YYYY') as SALES_DATE_1,

    Month(DateID) as SALES_MONTH,

    Year(DateID) as SALES_YEAR,

    MonthName(DateID) as SALES_MONTHNAME,

    'Q' & Ceil(Month(DateID)/3) AS SALES_QUARTER,

    DayNumberOfYear(DateID) as DayNumberOfYear,

    Day(DateID) as DayNumberOfMonth,

    DayNumberOfQuarter(DateID) as DayNumberOfQuarter,

    num(Month(DateID)) as MonthNum

    Resident Cal;

drop Table Cal;

Thanks,

AS

[#3]

Thanks for the quick response amit

I have a calendar generated, so no drama with that, but an trying to join the dates to the dataset.

I know memory can be  problem, but essentially I have 1000 rows of data, with 365 dates, then I expect to end up with a table with 365000 rows.

[simenkg · Accepted solution]

I'm not sure I understand your problem.

Do you want all your 1000 rows of data to exist for all 365 days?

if so:

let vStartDate = num(MakeDate(2013,1,1));

let vEndDate = num(Today());

Fact:

load

Area

District

Version

Design ID

from Fact.qvd (qvd);

Dates:

load Date($(vStartDate) + RowNo()) as Date autogenerate $(vEndDate)-$(vStartDate);

outer join(Fact)

load * Resident Dates;

drop table Dates;

It might not be 100% correct but should show you the concept.

Regards

SKG

[#5]

Thanks Simen

it was the outer join that I was missing...I now have a complete dataset...appreciate your time mate