How to show only one record instead of multiple records based on a value of a date field

[vvira1316]

Hi,

I've a situation where I have to select a record based on following condition from multiple records. If END_DT is blank then that record should be displayed. If there is no blank END_DT but there are records with value in END_DT then record with latest END_DT should be shown.

I'm including the query and screen shot for your reference.

LEAD_STUDY_MANAGER:LOAD *;SQL SELECT
     PTCL.ROW_ID As PROTOCOL_ROW_ID,
     PTCL.PTCL_NUM AS PROTOCOL_NUM,
     PTCL.PTCL_NAME AS PROTOCOL_NAME,
     p.name,
     p.pr_emp_id,
     cnt.fst_name,
     cnt.last_name,
     pp.start_dt,
     pp.end_dt,
     pp.role_cd
from
     siebel.s_pt_pos_hst_ls pp,
     siebel.s_postn p,
     siebel.s_contact cnt,
     siebel.s_cl_ptcl_ls ptcl
where
     ptcl.ptcl_num=ptcl.ptcl_name and
     ptcl.ptcl_stat_cd <> 'Cancelled' and
     ptcl.row_id=pp.cl_ptcl_id(+) and
     pp.postn_id= p.row_id(+) and
     p.pr_emp_id=cnt.row_id(+) and
     ptcl.ptcl_type_cd in ('Company Sponsored','CCT','Clinical Pharmacology') and
     pp.role_cd = 'Lead Study Manager';

So in above example I have to show only last record instead of all three. If last record had date (04/01/2012) then the record with 03/02/2014 should be shown.

Any suggestions will be helpful.

Thanks,

Vijay

[sreenivas]

PFA file

[juleshartley]

You can use expressions such as this to find the maximum of a field across a dimension:

= Date(

          Max (

          Aggr(Max(End_DT), Product)

               )

     'DD/MM/YYYY')

You then need to handle null values (this is probably easier by substituting them in the load script.

Hiding rows based on this is then a case of adding a calculated dimension and/or set analysis.

[Josh_Good]

If you remove the Lead Study Manager field, I believe you will get what you are looking for.  If you do, then you can bring that field back into the table by bringing it in as an expression which selects the correct Lead Study Manager based on the end date logic you described above.

[atsushi_saijo]

The situation is: a product (CC-5013) has many End Date or NULL. I interpreted that PROTOCOL_NUM = Study Code. How would you say for such ploy:

//It is well known SAP practice to assign 9999...date. Assigning dummy date for NULL value.

LEAD_STUDY_MANAGER:

     LOAD *;SQL SELECT

     ............

     IF(ISNULL(pp.end_dt)=-1,MAKEDATE(9999,1,1),pp.end_dt)  AS ENDDT.T,

     PROTOCOL_NUM & IF(ISNULL(pp.end_dt)=-1,MAKEDATE(9999,1,1),pp.end_dt) AS K1

     //END DATE, TEMPORAL

     .............

//and here, we create the maximum value.

TEMP1:

     LOAD

     PROTOCOL_NUM, MAX(ENDDT.T)

     RESIDENT LEAD_STUDY_MANAGER

     GROUP BY PROTOTOL_NUM;

MASTER:

LOAD PROTOCOL_NUM&ENDDT.T AS K2 RESIDENT TEMP1;

DROP TABLE TEMP1;

//make inner join to get the full, unique record.

INNER JOIN (MASTER)

LOAD *, IF(ENDDT.T=MAKEDATE(9999,1,1),NULL(),ENDDT.T) AS pp.end_dt

RESIDENT LEAD_STUDY_MANAGER;

In this loading script, you have 2 tables: with all multiple records (perhaps LEAD_STUDY_MANAGER is needed for other QV grid purpose); and another, unique-latest end date records.