finding statut value where max date?

[#1]

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

[chematos]

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