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

Timestamp Range Lookup -- Help Please

Hi Folks, I am trying to add the SHIFT_NAME based on the Timestamp. Below is the time Ranges.

6AM - 6PM --> SHIFT A

6PM - 6AM --> SHIFT B

Please find the below some Sample data:

ID  TIMESTAMP                 SHIFT_NAME

1   5/30/2013 07:10:10 AM   5/30/2013-A

2   5/30/2013 05:59:59 AM   5/29/2013-B

3   5/30/2013 02:19:68 AM   5/29/2013-B

4   5/29/2013 05:10:10 PM   5/29/2013-A

5   5/29/2013 12:10:34 PM   5/29/2013-A

6   5/29/2013 09:10:10 AM   5/29/2013-A

The Bold values are desired out put.

I have very large data set around 4M of data. So please provide best optimal way to achieve this.

1 Solution

Accepted Solutions
Not applicable
Author

Thanks Vivien.

LOAD IF(HOUR(TIMESTAMP) < 6,DATE(TIMESTAMP-1)&'-B',IF(HOUR(TIMESTAMP)>=6 AND HOUR(TIMESTAMP)<=18,DATE(TIMESTAMP)&'-A',DATE(TIMESTAMP)&'-B')) AS SHIFT;

View solution in original post

3 Replies
vivientexier
Partner - Creator II
Partner - Creator II

LOAD

     Date(TIMESTAMP, 'D/MM/YYYY') & If(Hour(TIMESTAMP)>=6 and Hour(TIMESTAMP)<18, '-A', '-B') as SHIFT_NAME

From ...

Not applicable
Author

Thanks Vivien.

LOAD IF(HOUR(TIMESTAMP) < 6,DATE(TIMESTAMP-1)&'-B',IF(HOUR(TIMESTAMP)>=6 AND HOUR(TIMESTAMP)<=18,DATE(TIMESTAMP)&'-A',DATE(TIMESTAMP)&'-B')) AS SHIFT;

vivientexier
Partner - Creator II
Partner - Creator II

auto-congratulation ?! interesting.