Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Cannot extract only some parts of a timestamp

In table Stamps, I store all the hours (as a timestamp i.e. date with time) in 2014. So, I have 365 * 24 = 8760 rows in the table. I want to extract only one of the parts of a timestamp, at a time -

(1) Date only

(2) Time only

(3) Hours only

In tried to do (1) first. I was hoping it would give me 365 rows, but it gives me 8760 instead. How do I correct this and how do I do (2) and (3) ?

Code for (1) -

[Tmp_Dates_2014]:

LOAD Date(Floor(TStamp)) as [DateOnly]

Resident [Web Traffic] Order By [TStamp];

Thanks.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Are you looking for a DISTINCT load maybe?

[Tmp_Dates_2014]:

LOAD DISTINCT Date(Floor(TStamp)) as [DateOnly]

Resident [Web Traffic] Order By [TStamp];

LOAD DISTINCT Time(Frac(TStamp)) as [TimeOnly]

Resident [Web Traffic] Order By [TStamp];

LOAD DISTINCT Hour(TStamp) as [HourOnly]

Resident [Web Traffic] Order By [TStamp];

View solution in original post

3 Replies
swuehl
MVP
MVP

Are you looking for a DISTINCT load maybe?

[Tmp_Dates_2014]:

LOAD DISTINCT Date(Floor(TStamp)) as [DateOnly]

Resident [Web Traffic] Order By [TStamp];

LOAD DISTINCT Time(Frac(TStamp)) as [TimeOnly]

Resident [Web Traffic] Order By [TStamp];

LOAD DISTINCT Hour(TStamp) as [HourOnly]

Resident [Web Traffic] Order By [TStamp];

Not applicable
Author

Thanks ! That was fast   Makes sense - when you strike off the time part from a date, only the date part remains and the date is repeated 24 times. What was I thinking.

maxgro
MVP
MVP

1) add a distinct

[Tmp_Dates_2014]:

LOAD distinct

Date(Floor(TStamp)) as [DateOnly]

Resident [Web Traffic] Order By [TStamp];

2) distinct time#(time(TStamp, 'hh:mm:ss'))

3) distinct hour(TStamp)