Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Re: Timestamp Range Lookup -- Help Please

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;

3 Replies
vivientexier
Contributor II

Re: Timestamp Range Lookup -- Help Please

LOAD

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

From ...

Not applicable

Re: Timestamp Range Lookup -- Help Please

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
Contributor II

Re: Timestamp Range Lookup -- Help Please

auto-congratulation ?! interesting.

Community Browser