canonical date

[Anonymous]

hi,

i have below table and i want to used same calendar for both date.

  Order

Row ID	Order ID	Order Date	Ship Date
32298	CA-2012-124891	7/31/2012	7/31/2012
26341	IN-2013-77878	2/5/2013	2/7/2013
25330	IN-2013-71249	10/17/2013	10/18/2013
13524	ES-2013-1579342	1/28/2013	1/30/2013

please help me to find out solution.

Thank you..

[its_anandrjs]

You can load your data this way also

Data:

LOAD * INLINE [

    Order Row ID, Order ID, Order Date, Ship Date

    32298, CA-2012-124891, 7/31/2012, 7/31/2012

    26341, IN-2013-77878, 2/5/2013, 2/7/2013

    25330, IN-2013-71249, 10/17/2013, 10/18/2013

    13524, ES-2013-1579342, 1/28/2013, 1/30/2013

];

CrossTable(DateFlag, Dates, 2)

LOAD [Order Row ID], [Order ID], [Order Date], [Ship Date]

Resident Data;

DROP Table Data;

Find attached

[its_anandrjs]

You can load with Cross Table load as well

See the attached example script.

Data:

LOAD [Order Row ID], [Order ID], Date(Date#([Order Date]),'MM/DD/YYYY') as [Order Date], Date(Date#([Ship Date]),'MM/DD/YYYY') as [Ship Date] INLINE [

    Order Row ID, Order ID, Order Date, Ship Date

    32298, CA-2012-124891, 07/31/2012, 07/31/2012

    26341, IN-2013-77878, 02/05/2013, 02/07/2013

    25330, IN-2013-71249, 10/17/2013, 10/18/2013

    13524, ES-2013-1579342, 01/28/2013, 01/30/2013

];

New:

CrossTable(DateFlag, Dates, 2)

LOAD [Order Row ID], [Order ID], [Order Date], [Ship Date]

Resident Data;

DROP Table Data;

[its_anandrjs]

Another Best way to load this ways and create the master calendar also you have to use the DateFlag flag field while calculation because after this you get single date filed and that is connected with your calendar table with other. This will helpful to you in the missing data as well.

Script:-

Data:

LOAD [Order Row ID], [Order ID], Date(Date#([Order Date]),'MM/DD/YYYY') as [Order Date], Date(Date#([Ship Date]),'MM/DD/YYYY') as [Ship Date] INLINE [

    Order Row ID, Order ID, Order Date, Ship Date

    32298, CA-2012-124891, 07/31/2012, 07/31/2012

    26341, IN-2013-77878, 02/05/2013, 02/07/2013

    25330, IN-2013-71249, 10/17/2013, 10/18/2013

    13524, ES-2013-1579342, 01/28/2013, 01/30/2013

];

New:

CrossTable(DateFlag, Dates, 2)

LOAD [Order Row ID], [Order ID], [Order Date], [Ship Date]

Resident Data;

DROP Table Data;

//Master Calendar

MinMaxDate:

LOAD Min(Dates) as MinDate, Max(Dates) as MaxDate Resident New;

LET vMinDate = Peek('MinDate',0,'MinMaxDate');

LET vMaxDate = Peek('MaxDate',0,'MinMaxDate');

Temp:

LOAD

Date($(vMinDate) + IterNo() -1 ) as Dates,

Year(Date($(vMinDate) + IterNo() -1 )) as Year,

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

AutoGenerate 1

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

DROP Table MinMaxDate;

[Anonymous]

i am not able to read file please share screenshot.