Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello community! I need help with the following situation:
I have the following table stores the errors in a production process:
stops |
---|
id_stop |
error_description |
start_time |
end_time |
duration |
My problem is, I need to identify how many errors occur per shift, but shifts currently we not stored anywhere.
the information its should be, for example:
date | shift | shif_group |
---|---|---|
2013-01-01 | morning | A |
2013-01-01 | evening | B |
2013-01-01 | night | C |
2013-01-02 | night | C |
Morning shifts are from 6 am to 14 pm.
the evening from 14 PM to 22 PM.
the night from 22 PM to 6 PM.
I'm trying to do the following:
select id_stop, error_description, start_time, end_time, duration,
shift= CASE
when DatePart(hh, start_time) between 22 and 23 then 'Night'
when DatePart(hh, start_time) between 00 and 05 then 'Night'
when DatePart(hh, start_time) between 06 and 13 then 'Morning'
when DatePart(hh, start_time) between 14 and 21 then 'Evening'
end
from stops
where start_time >= '2013-01-01' ;
But I have an error, "the column shift does not exist".
Can anyone help me out in this problem? or perhaps with a better idea how solved this 🙂
from already thank you very much!
Check out IntervalMatch funtion in qlikview help
The SELECT statement is evaluated by the database, not by QlikView. If you want to make the SELECT work, you need to check the documentation of your database.
Otherwise you can put the same logic in an if-function in a preceding Load. See more on http://community.qlik.com/blogs/qlikviewdesignblog/2013/03/04/preceding-load
HIC