Last Event Date Blank if only one Date

[#1]

Hi,

I have the following code and it works well but there is a problem. If there is only InputFirst and no InputLast for an application then the code under InputLast shows the same date of InputFirst. Actually InputLast should be blank. Is it possible to show InputLast blank if there is only InputFirst.

Wrkflow:
LOAD   Process_Code ,
Application_id   as   Application_Id ,
Workitem_Id ,
Activity_Code ,
Timestamp ( Txn_Date ) as   Txn_Date ,
State_Code ,
Assigned_User_Id ,
Assigned_Dept_Id ,
Actioned_User_Id ,
applymap ( 'Trays' , Activity_Code , null ()) as   Tray ,
applymap ( 'Trays' , Activity_Code , null ())& State_Code   as   Link ,
if ( State_Code = 'CMP' , timestamp ( Txn_Date ), null ()) as   [CMP_Date]

FROM

( txt , codepage   is   1252 , embedded   labels , delimiter   is   '\t' , msq ) ;

InputFirst:
LOAD
Application_Id,
FirstValue(Txn_Date) as InputFirstTimestamp
resident Wrkflow
where Activity_Code = 'NBG' and State_Code = 'RDY'
group by Application_Id
order by Txn_Date;

InputLast:
LOAD
Application_Id,
LastValue(Txn_Date) as InputLastTimestamp
resident Wrkflow
where Activity_Code = 'NBG' and State_Code = 'RDY'
group by Application_Id
order by Txn_Date;

[MK_QSL]

JOIN

LOAD
Application_Id,

Count(Txn_Date) as CntTxn_Date

resident Wrkflow
where Activity_Code = 'NBG' and State_Code = 'RDY'
group by Application_Id
order by Txn_Date;

InputFirst:
LOAD
Application_Id,
FirstValue(Txn_Date) as InputFirstTimestamp
resident Wrkflow
where Activity_Code = 'NBG' and State_Code = 'RDY'
group by Application_Id
order by Txn_Date;

InputLast:
LOAD
Application_Id,
IF(CntTxn_Date) = 1 , '', LastValue(Txn_Date)) as InputLastTimestamp
resident Wrkflow
where Activity_Code = 'NBG' and State_Code = 'RDY'
group by Application_Id
order by Txn_Date;

[#3]

you could use an if statement, so (if(LastValue(Txn_Date) = FirstValue(Txn_Date), '', LastValue(Txn_Date))

[#4]

Hi Manish,

Do you want me to merge my code and your code as below:

Wrkflow:
LOAD   Process_Code ,
Application_id   as   Application_Id ,
Workitem_Id ,
Activity_Code ,
Timestamp ( Txn_Date ) as   Txn_Date ,
State_Code ,
Assigned_User_Id ,
Assigned_Dept_Id ,
Actioned_User_Id ,
applymap ( 'Trays' , Activity_Code , null ()) as   Tray ,
applymap ( 'Trays' , Activity_Code , null ())& State_Code   as   Link ,
if ( State_Code = 'CMP' , timestamp ( Txn_Date ), null ()) as   [CMP_Date]

FROM

( txt , codepage   is   1252 , embedded   labels , delimiter   is   '\t' , msq ) ;

InputFirst:
LOAD
Application_Id,
FirstValue(Txn_Date) as InputFirstTimestamp
resident Wrkflow
where Activity_Code = 'NBG' and State_Code = 'RDY'
group by Application_Id
order by Txn_Date;

InputLast:
LOAD
Application_Id,
LastValue(Txn_Date) as InputLastTimestamp
resident Wrkflow
where Activity_Code = 'NBG' and State_Code = 'RDY'
group by Application_Id
order by Txn_Date;

JOIN

LOAD
Application_Id,

Count(Txn_Date) as CntTxn_Date

resident Wrkflow
where Activity_Code = 'NBG' and State_Code = 'RDY'
group by Application_Id
order by Txn_Date;

InputFirst:
LOAD
Application_Id,
FirstValue(Txn_Date) as InputFirstTimestamp
resident Wrkflow
where Activity_Code = 'NBG' and State_Code = 'RDY'
group by Application_Id
order by Txn_Date;

InputLast:
LOAD
Application_Id,
IF(CntTxn_Date) = 1 , '', LastValue(Txn_Date)) as InputLastTimestamp
resident Wrkflow
where Activity_Code = 'NBG' and State_Code = 'RDY'
group by Application_Id
order by Txn_Date;

[#5 · Accepted solution]

Hi Manish,

Thank you so much. I edited your code and used the following and it worked. Thanks a lot.

Wrkflow:
LOAD   Process_Code ,
Application_id   as   Application_Id ,
Workitem_Id ,
Activity_Code ,
Timestamp ( Txn_Date ) as   Txn_Date ,
State_Code ,
Assigned_User_Id ,
Assigned_Dept_Id ,
Actioned_User_Id ,
applymap ( 'Trays' , Activity_Code , null ()) as   Tray ,
applymap ( 'Trays' , Activity_Code , null ())& State_Code   as   Link ,
if ( State_Code = 'CMP' , timestamp ( Txn_Date ), null ()) as   [CMP_Date]

FROM

( txt , codepage   is   1252 , embedded   labels , delimiter   is   '\t' , msq ) ;

InputFirst:
LOAD
Application_Id,
FirstValue(Txn_Date) as InputFirstTimestamp
resident Wrkflow
where Activity_Code = 'NBG' and State_Code = 'RDY'
group by Application_Id
order by Txn_Date;

InputLast:
LOAD
Application_Id,
IF(Count(Txn_Date) = 1 , '', LastValue(Txn_Date)) as InputLastTimestamp
resident Wrkflow
where Activity_Code = 'NBG' and State_Code = 'RDY'
group by Application_Id
order by Txn_Date;