topic Re: Master Calendar issue in QlikView

Master Calendar issue

arsallee3 — Wed, 20 Apr 2016 17:18:22 GMT

Good day, working on some new development and having problem understanding why Master Calendar is not working.

Original field load is date time stamp, loading this as is along with additionally load date and time only.

Creating master calendar to the date only load and this creates fine and links fine however when I select this date out of the master calendar I get no results on simple sum of quantity.

sample data of qvf attached.

I appreciate any assistance.

Re: Master Calendar issue

swuehl — Wed, 20 Apr 2016 18:15:21 GMT

Your TicketDateNum is a Timestamp, with date and time part.

If you then create your master calendar, most of the timestamps won't match with your calendar values.

Take care to create a Datefield with only date part:

[TicketTable]:

LOAD [TicketId],

[HaulerId],

[TicketDate],

[TicketDateTime],

Floor([TicketDateNum]) as [TickerDateNum],

[WaterTypeId],

[OperatorId],

[Quantity],

[DisposalId],

[TicketNo],

[DeliveryType]

FROM [lib://FolderQVD (bosque_asallee)/TicketTable.QVD]

(qvd) Where TicketDateNum >= '$(vMonthStart)' and TicketDate < '$(vToday)';

Also check your other date fields if they have a numeric representation as requested (i.e. integer values for date fields, floating point for timestamps / time values).

Why don’t my dates work?

Re: Master Calendar issue

arsallee3 — Wed, 20 Apr 2016 19:11:35 GMT

Hmm that is strange, here is how I am loading the QVD directly from the database, do I need to convert to date when I run the load from this QVD file also you think?

TicketTable:

LOAD TicketId,

HaulerId,

TicketDate, (original date time field)

Time(TicketDate) as TicketDateTime,

Date(TicketDate) as TicketDateNum,

WaterTypeId,

OperatorId,

Quantity,

DisposalId,

TicketNo,

DeliveryType;

SQL SELECT TicketId,

HaulerId,

TicketDate,

WaterTypeId,

OperatorId,

Quantity,

DisposalId,

TicketNo,

DeliveryType

FROM SWD.dbo.Ticket ;

STORE TicketTable INTO [lib://FolderQVD (bosque_asallee)/TicketTable.QVD] (qvd);

Drop Table TicketTable;

Re: Master Calendar issue

swuehl — Wed, 20 Apr 2016 19:33:05 GMT

Yes, Date() and Time() are just formatting functions and do not change the underlying value.

To only get date / time part:

LOAD TicketId,

HaulerId,

TicketDate, (original date time field)

Time(Frac(TicketDate)) as TicketDateTime,

Date(Floor(TicketDate)) as TicketDateNum,

WaterTypeId,

OperatorId,

Quantity,

DisposalId,

TicketNo,

DeliveryType;

Re: Master Calendar issue

arsallee3 — Wed, 20 Apr 2016 19:44:28 GMT

Got it, that solves all my issues!