topic Re: master calender in QlikView

master calender

Sat, 09 May 2015 13:56:37 GMT

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

Re: master calender

Anonymous — Sat, 09 May 2015 14:04:57 GMT

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.

Re: master calender

petter — Sat, 09 May 2015 14:05:12 GMT

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

Re: master calender

Sat, 09 May 2015 14:40:46 GMT

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);