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

problems with one CASE into one select

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:

dateshiftshif_group
2013-01-01morningA
2013-01-01eveningB
2013-01-01nightC
2013-01-02nightC

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!

Tags (1)
2 Replies
sushil353
Honored Contributor II

Re: problems with one CASE into one select

Check out IntervalMatch funtion in qlikview help

Employee
Employee

Re: problems with one CASE into one select

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

Community Browser