2 sources with own dates

[ziggy47dg]

I have 2 data sources with their own date formats.

1st main fact table has a format of dd/mm/yyyy and second only has a format of mm/yyyy as every date has been based on 1st of relevant month.

I have a method for selecting a date range based on calendar selection and this works well for my data directly related to my 1st fact table, however my second fact table, which is mapped by product key to the first but with the differnt date format, does not relate to the data range selected.

I therefore need to link the dates of my second fact table to those of my first as they share the product key variable.

[#2]

Hi,

For this you need to combine the date fromats of both the tables to DD/MM/YYYY. For this you need to convert the second table date format to DD/MM/YYYY from MM/YYYY by using the following script.

Ensure you change the settings of DateFormat as highlighted.

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

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

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

table1:

Load * Inline [

Date

01/03/2011

02/03/2011

15/03/2011

];

table2:

Load * Inline [

Date2

04/2011

05/2011

06/2011

];

tmp:

noconcatenate Load Date2, Date#(Date2,'MM/YYYY') as Dat REsident table2;

Drop Table table2;

Concatenate (table1) Load Dat, Date#(MakeDate(year(Dat),month(Dat),01),'DD/MM/YYYY') as Date resident tmp;

Drop table tmp;