Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

canonical date

hi,

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

  Order

     Row ID    Order ID   Order DateShip Date
32298CA-2012-1248917/31/20127/31/2012
26341IN-2013-778782/5/20132/7/2013
25330IN-2013-7124910/17/201310/18/2013
13524ES-2013-15793421/28/20131/30/2013

please help me to find out solution.

Thank you..

13 Replies
Highlighted
Master II
Master II

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;

Capture.JPG

Highlighted
Creator III
Creator III

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;

Highlighted
Creator III
Creator III

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

Highlighted
Creator III
Creator III

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

微信截图_20171101164323.png

Highlighted
Partner
Partner

Highlighted
Creator III
Creator III

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

   

Highlighted
Creator III
Creator III

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

Highlighted

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

Highlighted
Creator III
Creator III

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