Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
vengadeshpalani
Contributor

why it not mapping properly

Hi all

i try to get hour field in master calendar but Master calendar link key is  not mapping with Data Tables

Screenshot_2.png

What i did wrong? why its duplicated?

1 Solution

Accepted Solutions

Re: why it not mapping properly

Try this

GasResponse:

LOAD ISR_NO,

    CREATED_DATETIME,

    Timestamp(Floor(CREATED_DATETIME, 1/24),'MM/DD/YYYY HH') as MasCalLink,

    Timestamp(Floor(CREATED_DATETIME, 1/24),'MM/DD/YYYY HH') as MasCalLink1,

    RESPONSE_TIME

FROM

[try.xls]

(biff, embedded labels, table is Sheet1$);


Calendar:

LOAD Year(TimeStamp) as Year,

  MonthName(TimeStamp) as MonthYear,

  Month(TimeStamp) as Month,

  Day(TimeStamp) as Day,

  Date( Floor( Date(TimeStamp)),'MM/DD/YYYY') as Date,

  Hour(TimeStamp) AS Hour,

  Timestamp(Floor(TimeStamp, 1/24),'MM/DD/YYYY HH') as MasCalLink ;

LOAD

    Timestamp($(vMinDate) + (RecNo() - 1)/24) as TimeStamp

AUTOGENERATE 23 * $(vDays);

Capture.PNG

View solution in original post

4 Replies
MVP
MVP

Re: why it not mapping properly

That's because you only formatted the text representation, and the numeric part kept unchanged (and the numeric part is used for the key match).

try to adapt the numeric part, too, e.g.

Timestamp#(Timestamp(TimeStamp,'MM/DD/YYYY HH'),'MM/DD/YYYY HH') as MasCalLink

Re: why it not mapping properly

Try this

GasResponse:

LOAD ISR_NO,

    CREATED_DATETIME,

    Timestamp(Floor(CREATED_DATETIME, 1/24),'MM/DD/YYYY HH') as MasCalLink,

    Timestamp(Floor(CREATED_DATETIME, 1/24),'MM/DD/YYYY HH') as MasCalLink1,

    RESPONSE_TIME

FROM

[try.xls]

(biff, embedded labels, table is Sheet1$);


Calendar:

LOAD Year(TimeStamp) as Year,

  MonthName(TimeStamp) as MonthYear,

  Month(TimeStamp) as Month,

  Day(TimeStamp) as Day,

  Date( Floor( Date(TimeStamp)),'MM/DD/YYYY') as Date,

  Hour(TimeStamp) AS Hour,

  Timestamp(Floor(TimeStamp, 1/24),'MM/DD/YYYY HH') as MasCalLink ;

LOAD

    Timestamp($(vMinDate) + (RecNo() - 1)/24) as TimeStamp

AUTOGENERATE 23 * $(vDays);

Capture.PNG

View solution in original post

tripatirao
Contributor II

Re: why it not mapping properly

Hi Sunny,

How below code works

LOAD

    Timestamp($(vMinDate) + (RecNo() - 1)/24) as TimeStamp

AUTOGENERATE 23 * $(vDays);



Can you please explain


Regards

Tripati

Re: why it not mapping properly

For a fixed series of vDays full day calendar entries (all hours), you may want to change the 23 into 24.