Master Calendar issue

[arsallee3]

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.

[swuehl · Accepted solution]

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?

[arsallee3]

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;

[swuehl]

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;

[arsallee3]

Got it, that solves all my issues!