Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
vijitbhargav
Contributor III
Contributor III

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

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.

1 Solution

Accepted Solutions
Henric_Cronström

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

View solution in original post

11 Replies
jagan
Luminary Alumni
Luminary Alumni

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.

vijitbhargav
Contributor III
Contributor III
Author

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
Luminary Alumni
Luminary Alumni

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.

Henric_Cronström

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

View solution in original post

vijitbhargav
Contributor III
Contributor III
Author

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.

vijitbhargav
Contributor III
Contributor III
Author

Thanks Henric. It worked.

Not applicable

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)

Henric_Cronström

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

Not applicable

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