topic Timestamp Range Lookup -- Help Please in QlikView

Timestamp Range Lookup -- Help Please

Thu, 30 May 2013 19:32:34 GMT

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.

Re: Timestamp Range Lookup -- Help Please

vivientexier — Thu, 30 May 2013 19:42:16 GMT

LOAD

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

From ...

Re: Timestamp Range Lookup -- Help Please

Thu, 30 May 2013 20:50:34 GMT

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;

Re: Timestamp Range Lookup -- Help Please

vivientexier — Fri, 31 May 2013 05:03:27 GMT

auto-congratulation ?! interesting.