crosstable link to calendar table and running total on MAT

[#1]

Hi ,

I would appreciate urgent help with the following

I have loaded a crosstable into the model with success, but my linking to my master calendar doesn't work and i need to calculate running total for 5 MAT periods. See my attached word document for detail description.

Thank so much

louw

[pover]

Louw,

The solution looks reasonable, but note that when you use the crosstable() function with a table that has columns with dates the data in the resulting table is going to be a string and not a date. So, after doing the crosstable, I would call the resulting table to transform the dates as you did in the master calendar.

(Date(Date#(TempDate,'DD/MM/YYYY'),'DD/MM/YYYY')) as Dates,

After transforming the format then I would continue with making the master calendar. You then shouldn't have to do the date#() function in the master calendar.

Regards.

[#3]

Hi Karl,

Thank you for the reply. Where will i put this transform statement in the crosstable.

Transactions :

CrossTable(Dates , Data, 14)

LOAD CORPORATION,

MANUFACTURER,

SEGMENT,

PRD_TYPE,

MOLECULE,

BRAND,

PACK,

PRODUCT,

SCHEDULE,

LAUNCH_DATE,

ATC1,

ATC2,

ATC3,

ATC4,

Date(Date#('2005/12/01','DD/MM/YYYY'),'DD/MM/YYYY') as Dates, - with every date below this ?

// [2005/12/01],

// [2006/01/01],

// [2006/02/01],

// [2006/03/01],

// [2006/04/01],

// [2006/05/01],

OR ELSE WHERE ?

thank you.

Louw

[pover]

Louw,

First load the crosstable and then use resident to call it again and make the transformation:

Tmp_Transactions :

CrossTable(Tmp_Dates , Data, 14)

LOAD CORPORATION,

MANUFACTURER,

SEGMENT,

PRD_TYPE,

MOLECULE,

BRAND,

PACK,

PRODUCT,

SCHEDULE,

LAUNCH_DATE,

ATC1,

ATC2,

ATC3,

ATC4,

[2005/12/01],

[2006/01/01],

[2006/02/01],

[2006/03/01],

[2006/04/01],

[2006/05/01]

From table.xls;

Transactions:

Load *,Date(Date#(Tmp_Dates,'DD/MM/YYYY'),'DD/MM/YYYY') as Dates

Resident Tmp_Transactions;

Drop table Tmp_Transactions;

Drop field Tmp_Dates from Transactions;

And then do the master calendar...

Regards.

[#5]

Hi Karl,

I have implemented your suggestion and this seems to be the solution except the Dates is not returning any value , it return all fields but the Dates in my case %key field returns just -, as soon as i just put in Tmp_Dates it returns a value but obviously the link to calendar table is wrong. See my code below. Thank you for your help.

SET ThousandSep=' ';

SET DecimalSep='.';

SET MoneyThousandSep=' ';

SET MoneyDecimalSep=',';

SET MoneyFormat='R #,##0.00;R-#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Tmp_Transactions :

CrossTable(Tmp_Dates , Data, 14)

LOAD CORPORATION,

MANUFACTURER,

SEGMENT,

PRD_TYPE,

MOLECULE,

BRAND,

PACK,

PRODUCT,

SCHEDULE,

LAUNCH_DATE,

ATC1,

ATC2,

ATC3,

ATC4,

[2005/12/01],

[2005/12/01],

[2006/01/01],

[2006/02/01],

[2006/03/01],

[2006/04/01],

[2006/05/01],

[2006/06/01],

[2006/07/01],

[2006/08/01],

[2006/09/01],

[2006/10/01],

[2006/11/01],

[2006/12/01],

[2007/01/01],

[2007/02/01],

[2007/03/01],

[2007/04/01],

[2007/05/01],

[2007/06/01],

[2007/07/01],

[2007/08/01],

[2007/09/01],

[2007/10/01],

[2007/11/01],

[2007/12/01],

[2008/01/01],

[2008/02/01],

[2008/03/01],

[2008/04/01],

[2008/05/01],

[2008/06/01],

[2008/07/01],

[2008/08/01],

[2008/09/01],

[2008/10/01],

[2008/11/01],

[2008/12/01],

[2009/01/01],

[2009/02/01],

[2009/03/01],

[2009/04/01],

[2009/05/01],

[2009/06/01],

[2009/07/01],

[2009/08/01],

[2009/09/01],

[2009/10/01],

[2009/11/01],

[2009/12/01],

[2010/01/01],

[2010/02/01],

[2010/03/01],

[2010/04/01],

[2010/05/01],

[2010/06/01],

[2010/07/01],

[2010/08/01],

[2010/09/01],

[2010/10/01],

[2010/11/01]

FROM

C:\Users\ Desktop\Temp1.xlsx

(ooxml, embedded labels, table is Temp1_Value);

Transactions:

Load *,Date(Date#(Tmp_Dates,'DD/MM/YYYY'),'DD/MM/YYYY') as %_DateKey

//Load *,Tmp_Dates as %_DateKey

Resident Tmp_Transactions;

Drop table Tmp_Transactions;

Drop field Tmp_Dates from Transactions;

MATTable:

LOAD * INLINE [

;

LET vMaxDate = num(today(1)); // End at today's date

/*

LET vMinDate = num(MakeDate(2005,12,1)); // Start at 2006-01-01.

//------------- Temporary Calendar ----------------

TempCalendar:

LOAD

$(vMinDate)+IterNo()-1 as Num,

Date($(vMinDate)+IterNo()-1) as TempDate

AUTOGENERATE 1 WHILE ($(vMinDate)+IterNo()-1) <= $(vMaxDate);

//------------- Temporary Calendar ----------------

MasterCalendar:

LOAD TempDate as Date,

Date(Date#(TempDate,'DD/MM/YYYY'),'DD/MM/YYYY') as %_DateKey,

week(TempDate) as Week,

year(TempDate) as Year,

month(TempDate) as Month,

day(TempDate) as Day,

weekday(TempDate) as WeekDay,

'Q' & ceil(month(TempDate)/3) as Quarter,

year(TempDate)&'-'&'Q' & ceil(month(TempDate)/3) as YearQuarter,

date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

date(monthstart(TempDate), 'MMM-YY') as MonthYearShort,

week(TempDate)&'-'&Year(TempDate) as WeekYear,

Year2Date(TempDate, 0, 1, $(vToday))*-1 as CurYTDFlag,

Year2Date(TempDate,-1, 1, $(vToday))*-1 as LastYTDFlag,

num(month(TempDate),'00') as Period,

DayNumberOfYear(TempDate) as DayNumberOfYear

RESIDENT TempCalendar

ORDER BY TempDate Asc;

DROP TABLE TempCalendar;

Inner Join (MasterCalendar) IntervalMatch ( %_DateKey ) Load Start, End Resident MATTable;

Left Join (MasterCalendar) LOAD * Resident MATTable;

DROP Table MATTable;

[pover]

The date#() function should contain the format of the string. In your case it's YYYY/MM/DD so the formula would be

Date(Date#(Tmp_Dates,'YYYY/MM/DD'),'DD/MM/YYYY') as %_DateKey

Regards.

[#7]

Karl thank so much! it worked.

You just made my day.

Regards

Louw