topic Re: Autogenerating dates within a fact table in QlikView

Autogenerating dates within a fact table

Wed, 16 Jul 2014 11:17:27 GMT

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

Re: Autogenerating dates within a fact table

amit_saini — Wed, 16 Jul 2014 11:21:23 GMT

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,

Re: Autogenerating dates within a fact table

Wed, 16 Jul 2014 11:25:53 GMT

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.

Re: Autogenerating dates within a fact table

simenkg — Wed, 16 Jul 2014 12:19:10 GMT

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

Re: Autogenerating dates within a fact table

Fri, 18 Jul 2014 00:56:53 GMT

Thanks Simen

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