topic Re: canonical date in QlikView

canonical date

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: canonical date

devarasu07 — Wed, 01 Nov 2017 08:21:45 GMT

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;

Re: canonical date

woshua5550 — Wed, 01 Nov 2017 08:27:35 GMT

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;

Re: canonical date

Anonymous — Wed, 01 Nov 2017 08:37:39 GMT

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

Re: canonical date

woshua5550 — Wed, 01 Nov 2017 08:45:20 GMT

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

Re: canonical date

vikasdesai — Wed, 01 Nov 2017 09:00:54 GMT

Hello Prachi

Please go to this link

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

Regards

Vikas

Re: canonical date

Anonymous — Wed, 01 Nov 2017 09:38:57 GMT

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

Re: canonical date

woshua5550 — Wed, 01 Nov 2017 09:57:24 GMT

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

Re: canonical date

its_anandrjs — Wed, 01 Nov 2017 10:07:11 GMT

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

Re: canonical date

Anonymous — Wed, 01 Nov 2017 10:12:11 GMT

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

Re: canonical date

its_anandrjs — Wed, 01 Nov 2017 10:30:10 GMT

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

Re: canonical date

its_anandrjs — Wed, 01 Nov 2017 10:37:09 GMT

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;

Re: canonical date

its_anandrjs — Wed, 01 Nov 2017 14:13:12 GMT

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;

Re: canonical date

Anonymous — Thu, 02 Nov 2017 05:57:06 GMT

i am not able to read file please share screenshot.