finding statut value where max date?

Mon, 30 Jul 2012 11:39:32 GMT

Hello,

I'm stuck on an insoluble problem for me. the need is very simple, but the context is less. I have three tables merged to create the table "incidents" and then I wanted to add a column showing the current status of the incident (p. "NUMBER").

I wrote a SQL query, which works fine with min, but not at all with max (no recording back). So I can have the first status of the incident and not the latest status. I do not understand why ... even after different tests and different tests.

(select a.TYPE from "SM_27_PRD_L".ACTIVITYM1 a WHERE a.DATESTAMP = (select max(a.DATESTAMP ) from "SM_27_PRD_L".ACTIVITYM1 a WHERE a."NUMBER" = p."NUMBER") ) as TEST3

I want to go around the problem by using SQL functions and not Qlikview to get the current status of the incident which is stored in the table "Table_Activity" I loaded into QlikView.

But I do not know at all how to do this. How can we find the value of the field "TYPE" table "Table_Activity" avyant date ("DATESTAMP") for the largest incident (N_Incident) of the table incident. (Incident)?

Someone could give me some help?

Thanks

Stéphane

Incidents:

Load

P_ID as N_Incident,

I_ID as i_id,

I_CLIENT as client,

I_OPEN_TIME as i_open_time,

I_OPENED_BY as i_opened_by,

if(I_CLOSE_TIME > date#('19000101', 'YYYYMMDD'), I_CLOSE_TIME, '') as i_close_time,

I_AFFECTED_ITEM as i_affected_item,

I_OPEN as i_status,

CONTACT_NAME as i_contact_name,

PRODUIT as i_produit,

PRIORITE as i_priorite,

OPERATEUR as i_operateur,

SUJET as i_sujet,

TYPE as i_type,

MOTIF as i_motif,

SM7_CLOSED_BY as i_sm7_closed_by,

OPEN_TIME as p_open_time,

OPENED_BY as p_opened_by,

if(CLOSE_TIME > date#('19000101', 'YYYYMMDD'), CLOSE_TIME, '') as p_close_time,

STATUS as p_status,

SM7_CLOSED_BY as p_sm7_closed_by,

ASSIGNMENT as p_assignment,

DATE1 as dateescalade,

TEST2 as resole_date,

TEST3 as Current_statut

;

sql select

p."NUMBER" as P_ID,

i.incident_id as I_ID,

i.open_time as I_OPEN_TIME,

i.opened_by as I_OPENED_BY,

nvl(i.close_time, '') as I_CLOSE_TIME,

i.affected_item as I_AFFECTED_ITEM,

d.company as I_CLIENT,

i.open as I_OPEN,

i.contact_name as CONTACT_NAME,

i.gen_ci_module as PRODUIT,

i.priority_code as PRIORITE,

i.owner_name as OPERATEUR,

i.title as SUJET,

i.category || ' - ' || i.subcategory || ' - ' || i.product_type as TYPE,

i.problem_type as MOTIF,

i.closed_by as SM7_CLOSED_BY,

d.company as I_CLIENT,

p.open_time as OPEN_TIME,

p.opened_by as OPENED_BY,

nvl(p.close_time, '') as CLOSE_TIME,

p.status as STATUS,

p.closed_by as SM7_CLOSED_BY,

p.ASSIGNMENT as ASSIGNMENT,

(select min(a.DATESTAMP) from "SM_27_PRD_L".ACTIVITYM1 a WHERE a."NUMBER" = p."NUMBER" ) as DATE1,

(select min(a.DATESTAMP) from "SM_27_PRD_L".ACTIVITYM1 a WHERE a."NUMBER" = p."NUMBER" and a.TYPE in ('Closed','Resolved')) as TEST2,

(select a.TYPE

from "SM_27_PRD_L".ACTIVITYM1 a

WHERE

a.DATESTAMP = (select max(a.DATESTAMP

) from "SM_27_PRD_L".ACTIVITYM1 a WHERE a."NUMBER" = p."NUMBER")

) as TEST3

from

"SM_27_PRD_L".SCRELATIONM1 r,

"SM_27_PRD_L".PROBSUMMARYM1 p,

"SM_27_PRD_L".incidentsm1 i,

device2m1 d

where

(p.ASSIGNMENT = 'GCE_RS'

or p.ASSIGNMENT = 'SUPPORT GCE')

and r.DEPEND LIKE ('IM%')

and r.DEPEND = p."NUMBER"

and d.logical_name = i.affected_item

and d.type = 'bizservice'

and i.category = 'incident'

and r.SOURCE = i.incident_id

and d.ASSIGNMENT = 'GCE_RS' ;

Table_Activity:

load NUMBER,

DATESTAMP,

OPERATOR,

TYPE,

DESCRIPTION;

SQL SELECT

(select max(b.DATESTAMP) from "SM_27_PRD_L".ACTIVITYM1 b Where b."NUMBER" = a."NUMBER") as DATESTAMP ,

a."NUMBER",

a.OPERATOR,

a.TYPE,

a.DESCRIPTION

FROM "SM_27_PRD_L".ACTIVITYM1 a

where

//a.TYPE in ('Status Change')

//AND

EXISTS

(SELECT 1

FROM "SM_27_PRD_L".PROBSUMMARYM1

WHERE (ASSIGNMENT = 'GCE_RS' ) and "NUMBER" = a."NUMBER")

Re: finding statut value where max date?

chematos — Mon, 30 Jul 2012 12:50:58 GMT

don´t you need to make a group by to get an aggregated function like max() ?

May be a solution, if group by doesn´t works, is to take the top 1 or the bottom 1 of that table ordering by datestamp

hope this helps

topic Re: finding statut value where max date? in QlikView

finding statut value where max date?

Re: finding statut value where max date?