problems with one CASE into one select

Thu, 07 Mar 2013 08:12:43 GMT

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!

Re: problems with one CASE into one select

sushil353 — Thu, 07 Mar 2013 08:20:46 GMT

Check out IntervalMatch funtion in qlikview help

Re: problems with one CASE into one select

hic — Fri, 08 Mar 2013 08:54:46 GMT

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

topic Re: problems with one CASE into one select in QlikView

problems with one CASE into one select

Re: problems with one CASE into one select

Re: problems with one CASE into one select