master calender

[#1]

hi

here iam created master calender but here no relation with actual table .my code is below shown.

orders:

LOAD OrderID,

    OrderDate,

    CustomerID,

    EmployeeID,

    ShipperID,

    Freight;

SQL SELECT *

FROM Orders;

STORE orders into QVD\orders.qvd(qvd);

sort:

LOAD * Resident orders Order by OrderID;

LET vmindate=Peek('OrderID',0,'sort');

LET vmaxdate=Peek('OrderID',-1,'sort');

temp:

LOAD Date($(vmindate)+RecNo()-1)as tempdate AutoGenerate $(vmaxdate)-$(vmindate)+1;

time:

LOAD tempdate as orderdate,

     Year(tempdate) as year,

     'Q'&Ceil(Month(tempdate)/3)as qtr,

     Month(tempdate)as month,

   Week(tempdate)as week,

    (Day(tempdate)) as day

     Resident temp;

  DROP Table temp;

here data not matching

[Anonymous]

First, you have to fix min and max date:

sort:

LOAD OrderDate Resident orders Order by OrderDate;

LET vmindate=Peek(OrderDate,0,'sort');

LET vmaxdate=Peek(OrderDate,-1,'sort');

Next, use the same field name in the calendar

LOAD tempdate as OrderDate

...

Probably there are other problems, but fix these first.

[petter]

QlikView is case sensitive when it comes to field names. So make sure your key-fields are matched by having right case that match...

Change orderdate into OrderDate by doing

   tempdate AS OrderDate

[#4]

orders:

LOAD OrderID,

    OrderDate,

    CustomerID,

    EmployeeID,

    ShipperID,

    Freight;

SQL SELECT *

FROM Orders;

STORE orders into QVD\orders.qvd(qvd);

Order:

LOAD * Resident orders Order by OrderID;

Min_OrderDate:

Load First 1 Min(Num(OrderDate) as Min_OrderDate

Resident orders;

Max_OrderDate:

Load First 1 Max(Num(OrderDate) as Max_OrderDate

Resident orders;

LET vmindate=Peek('Min_OrderDate',0,'Min_OrderDate');

LET vmaxdate=Peek('Max_OrderDate',0,'Max_OrderDate');

MasterCalender:

LOAD tempdate as OrderDate,

     Year(tempdate) as year,

     'Q'&Ceil(Month(tempdate)/3)as qtr,

     Month(tempdate)as month,

   Week(tempdate)as week,

    (Day(tempdate)) as day;

LOAD $(vmindate)+IterNo()-1as tempdate

AutoGenerate(1)

While $(vmindate)+IterNo()-1<=$(vmaxdate);