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")
