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..

[devarasu07]

Hi,

Try like below,

Data:

LOAD recno() as ID,* INLINE [

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

];

LinkTable:

LOAD

ID,

[Order Date] as Date,

'Order' as DateType

RESIDENT Data;

CONCATENATE (LinkTable)

LOAD

ID,

[Ship Date] as Date,

'Ship' as DateType

RESIDENT Data;

[woshua5550]

Not sure if this is what you need ,create a master calendar and associate with order table

Order:
LOAD *
    INLINE [
    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
];

CalendarTmp:
LOAD
Min([Order Date]) as D1,
Max([Ship Date]) as D2
Resident Order;

Let vMinDate = Peek('D1',0,'CalendarTmp');
Let vMaxDate = Peek('D2',0,'CalendarTmp');

MasterCalendar:
LOAD
Date(RowNo()+$(vMinDate)-1) as Date
AutoGenerate $(vMaxDate)-$(vMinDate)+1;

Left Join (Order)

IntervalMatch(Date)
LOAD
Distinct
[Order Date],
[Ship Date]
Resident Order;

DROP Table CalendarTmp;

[Anonymous]

i am unable to read QVW file, can you please attach image.

[woshua5550]

You can create a new qvw document , copy and paste my script and then reload

[vikasdesai]

Hello Prachi

Please go to this link

http://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date

Regards

Vikas

[Anonymous]

i am not getting one thing, what exactly output show in date field

   

[woshua5550]

Date field is a continuous calendar from minimum order date to maximum ship date

when you select one day on order date , it shows everyday from order day to ship date

[its_anandrjs]

Best explain by HIC on this thread about Canonical dates where you have not single date in your data model then load link table this ways.

Canonical Date

[Anonymous]

ohhhh..

but my query is i want to used order calendar on ship by using join so i can retrieve ship date value by using order date