Combine Date and Time Fields as a single field "TimeStamp"

[Anonymous]

I have two fields :

[Reporting Date]  and [Reporting Time]

How can I obtain a single field as TimeStamp from the above mentioned fields, during data load?

Consider [Reporting Date] = 02-05-2013 and [Reporting Time] = 09:55:10.

Timestamp should be - 02-05-2013 09:55:10.

Thank You.

[jagan]

Hi Vijit,

Try like this

LOAD

     *,

     Timestamp(Date#([Reporting Date] & ' ' & [Reporting Time], 'DD-MM-YYYY hh:mm:ss'))

FROM Data;

OR

LOAD

     *,

     Timestamp(Date#([Reporting Date] & ' ' & [Reporting Time], 'DD-MM-YYYY hh:mm:ss'), 'DD-MM-YYYY hh:mm:ss')

FROM Data;

Regards,

Jagan.

[Anonymous]

Hi Jagan,

Thanks for the solution. I am able to get the Timestamp.

However, the same is showing garbage value for Date. Please find below the situation :

[Reporting Date] = 02-04-2013, [Reporting Time] = 07:26:39

calculated TimeStamp is showing : 23-07-3032 07:26:39.

Any solution to this?

Thank You.

[jagan]

Hi Vijit,

It is working correctly for me check this in text object

=Timestamp(Date#('02-04-2013' & ' ' & '07:26:39', 'DD-MM-YYYY hh:mm:ss'), 'DD-MM-YYYY hh:mm:ss')

If not working can you attach some sample data file.

Regards,

Jagan.

[hic · Accepted solution]

I would prefer

   =Timestamp(Date#('02-04-2013','DD-MM-YYYY') + Time#('07:26:39','hh:mm:ss'), 'DD-MM-YYYY hh:mm:ss')

instead.

HIC

[Anonymous]

Hi Jagan,

As suggested by Henric, it is working after using "+".

But another situation I am facing here is the use of "#" after Date and Time function. No data is retreived if i use #.

Regards,

Vijit.

[Anonymous]

Thanks Henric. It worked.

[#8]

Hi Henric,

I have the following code which gives me CMP_Date and Pre.

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

Wrkflow2:
Load   * , 1
Resident   Wrkflow
Order   by   Application_Id , CMP_Date , Tray , State_Code;
DROP   Table   Wrkflow;

Wrkflow3:
Load   * ,
CMP_Date - if ( State_Code = 'CMP'  ,( Previous ( CMP_Date )), '' ) as   diff ,
networkdays (( Previous ( CMP_Date )), CMP_Date ) as   diff2 ,

if ( State_Code = 'CMP'  ,( Previous ( CMP_Date )), '' ) as   pre
Resident   Wrkflow2;
DROP   Table   Wrkflow2;

I am using CMP_Date and Pre in the following expression to get working hours and it is working well if we see application by application but it does not give correct average because there are zeros in each tray which reduces average. How can I ignore zeros in this scenerio please

sum({<Tray={'NAPS CJA Automatic Check'}>}fabs(interval(((Networkdays(Date(pre,'DD/MM/YYYY hh:mm:ss'),Date(CMP_Date,'DD/MM/YYYY hh:mm:ss')) -2)*'$(vWorkDay)')

+

if(frac(date(pre))<num('$(vQuitTime)'),if(frac(date(pre))>num('$(vStartTime)'),Date#(date(floor(pre),'DD/MM/YYYY') & '$(vQuitTime)' ,'DD/MM/YYYYhh:mm:ss')-Date(pre,'DD/MM/YYYY hh:mm:ss'),Date#(date(floor(pre),'DD/MM/YYYY') & '$(vQuitTime)' ,'DD/MM/YYYYhh:mm:ss')-Date#(date(floor(pre),'DD/MM/YYYY') & '$(vStartTime)' ,'DD/MM/YYYYhh:mm:ss')),0)

+

if(frac(date(CMP_Date))>num('$(vStartTime)'),if(frac(date(CMP_Date))<num('$(vQuitTime)'),(Date(CMP_Date,'DD/MM/YYYY hh:mm:ss')-Date#(date(floor(CMP_Date),'DD/MM/YYYY') & '$(vStartTime)','DD/MM/YYYYhh:mm:ss')),Date#(date(floor(CMP_Date),'DD/MM/YYYY') & '$(vQuitTime)','DD/MM/YYYYhh:mm:ss')-Date#(date(floor(CMP_Date),'DD/MM/YYYY') & '$(vStartTime)','DD/MM/YYYYhh:mm:ss')),0), 'hh:mm:ss'))*24)/count(distinct Application_Id)+

sum({<Tray={'NAPS Contact Customer'}>}fabs(interval(((Networkdays(Date(pre,'DD/MM/YYYY hh:mm:ss'),Date(CMP_Date,'DD/MM/YYYY hh:mm:ss')) -2)*'$(vWorkDay)')

+

if(frac(date(pre))<num('$(vQuitTime)'),if(frac(date(pre))>num('$(vStartTime)'),Date#(date(floor(pre),'DD/MM/YYYY') & '$(vQuitTime)' ,'DD/MM/YYYYhh:mm:ss')-Date(pre,'DD/MM/YYYY hh:mm:ss'),Date#(date(floor(pre),'DD/MM/YYYY') & '$(vQuitTime)' ,'DD/MM/YYYYhh:mm:ss')-Date#(date(floor(pre),'DD/MM/YYYY') & '$(vStartTime)' ,'DD/MM/YYYYhh:mm:ss')),0)

+

if(frac(date(CMP_Date))>num('$(vStartTime)'),if(frac(date(CMP_Date))<num('$(vQuitTime)'),(Date(CMP_Date,'DD/MM/YYYY hh:mm:ss')-Date#(date(floor(CMP_Date),'DD/MM/YYYY') & '$(vStartTime)','DD/MM/YYYYhh:mm:ss')),Date#(date(floor(CMP_Date),'DD/MM/YYYY') & '$(vQuitTime)','DD/MM/YYYYhh:mm:ss')-Date#(date(floor(CMP_Date),'DD/MM/YYYY') & '$(vStartTime)','DD/MM/YYYYhh:mm:ss')),0), 'hh:mm:ss'))*24)/count(distinct Application_Id)+

sum({<Tray={'NAPS Print Documents'}>}fabs(interval(((Networkdays(Date(pre,'DD/MM/YYYY hh:mm:ss'),Date(CMP_Date,'DD/MM/YYYY hh:mm:ss')) -2)*'$(vWorkDay)')

+

if(frac(date(pre))<num('$(vQuitTime)'),if(frac(date(pre))>num('$(vStartTime)'),Date#(date(floor(pre),'DD/MM/YYYY') & '$(vQuitTime)' ,'DD/MM/YYYYhh:mm:ss')-Date(pre,'DD/MM/YYYY hh:mm:ss'),Date#(date(floor(pre),'DD/MM/YYYY') & '$(vQuitTime)' ,'DD/MM/YYYYhh:mm:ss')-Date#(date(floor(pre),'DD/MM/YYYY') & '$(vStartTime)' ,'DD/MM/YYYYhh:mm:ss')),0)

+

if(frac(date(CMP_Date))>num('$(vStartTime)'),if(frac(date(CMP_Date))<num('$(vQuitTime)'),(Date(CMP_Date,'DD/MM/YYYY hh:mm:ss')-Date#(date(floor(CMP_Date),'DD/MM/YYYY') & '$(vStartTime)','DD/MM/YYYYhh:mm:ss')),Date#(date(floor(CMP_Date),'DD/MM/YYYY') & '$(vQuitTime)','DD/MM/YYYYhh:mm:ss')-Date#(date(floor(CMP_Date),'DD/MM/YYYY') & '$(vStartTime)','DD/MM/YYYYhh:mm:ss')),0), 'hh:mm:ss'))*24)/count(distinct Application_Id)

[hic]

Zeros should be included in the average. That is how the average is defined. If you want to remove the zeros, you can use If(Tray<>0,Tray) instead.

Further, I would strongly recommend that you try to simplify your expression...

HIC

[#10]

How can ifnore zeros if want to calculate (CMP_Date - pre)

if ( State_Code = 'CMP' , timestamp ( Txn_Date ), null ()) as [CMP_Date]

if ( State_Code = 'CMP' ,( Previous ( CMP_Date )), '' ) as pre