Wednesday, November 26, 2014

MAKING COMMON CALENDER in Qlikview



In northwind DB there are three dates:

1.order_date
2.shipping_date
3.required_date

We need to create a common calendar that would b common to all d dates if v
create a normal calendar using only order_date or so we will get the
corresponding shipping_date and required_date for that order date or so on
thus to select a date and get all order_date,shipping_date,required_date)
for that date v need to create a common calendar using concatenation

ex:

common_time:
LOAD OrderID,
     OrderDate,
     day(OrderDate) as day_of_OD,
     week(OrderDate) as week_of_OD,
     month(OrderDate) as Month_of_OD,
     year(OrderDate) as year_of_OD
FROM
[C:\Documents and Settings\11050.TCPL\Desktop\Almas\Orders.qvd]
(qvd);

concatenate

LOAD OrderID,
     ShippedDate,
     day(ShippedDate) as day_of_OD,
     week(ShippedDate) as week_of_OD,
     month(ShippedDate) as Month_of_OD,
     year(ShippedDate) as year_of_OD
FROM
[C:\Documents and Settings\11050.TCPL\Desktop\Almas\Orders.qvd]
(qvd);


concatenate

LOAD OrderID,
     RequiredDate,
     day(RequiredDate) as day_of_OD,
     week(RequiredDate) as week_of_OD,
     month(RequiredDate) as Month_of_OD,
     year(RequiredDate) as year_of_OD
FROM
[C:\Documents and Settings\11050.TCPL\Desktop\Almas\Orders.qvd]
(qvd);

//notice that v use d same names for all date columns  this will
concatenate the columns and create a table which holds all dates and each
date selected in this table will correspond to d same for all the three
dates

output:


OrderDate   
RequiredDate   
ShippedDate
1/9/1998
    1/5/1998
1/5/1998
1/8/1998
    1/6/1998
          1/6/1998
1/7/1998
    1/7/1998
          1/7/1998
1/6/1998
    1/8/1998
         1/8/1998
1/5/1998
    1/9/1998
         1/9/1998


No comments:

Post a Comment