topic Re: Combine Date and Time Fields as a single field "TimeStamp" in QlikView

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

Anonymous — Thu, 31 Oct 2013 04:49:35 GMT

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.

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

jagan — Thu, 31 Oct 2013 05:17:30 GMT

Hi Vijit,

Try like this

LOAD

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

FROM Data;

LOAD

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

FROM Data;

Regards,

Jagan.

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

Anonymous — Thu, 31 Oct 2013 06:37:01 GMT

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.

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

jagan — Thu, 31 Oct 2013 06:43:13 GMT

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.

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

hic — Thu, 31 Oct 2013 06:53:13 GMT

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

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

Anonymous — Thu, 31 Oct 2013 08:52:52 GMT

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.

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

Anonymous — Thu, 31 Oct 2013 08:53:11 GMT

Thanks Henric. It worked.

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

Mon, 24 Feb 2014 16:56:19 GMT

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

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

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

hic — Mon, 24 Feb 2014 17:09:49 GMT

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

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

Mon, 24 Feb 2014 17:12:59 GMT

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

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

Tue, 25 Feb 2014 09:50:16 GMT

Hi Henric,

Each application has different stages i.e. (Outbound ::::::::> Customer Time :::::::> Inbound ::::::::> Security::::::> Booked). Application can come in sequence in all trays once or more than once. Also application can skip any tray depending on product type. e.g. overdraft then it will not go in customer time tray and will move directly into inbound tray. Each tray is timestamp. I want to calculate difference between two trays timestamps e.g. (Customer Time - Outbound) in working hours. I got the following expression from qlikview community and it works well if I take application by application. But the average is incorrect because application can skip any tray or only in one tray and has to move in the next tray. So the next tray counts as 0 and then zero lowers the average.

In the following expression saying if WorkflowElapseDays='Inbound' then take the difference between CMP_Date (completion timestamp of Inbound tray) and Pre Date (previous tray timestamp, could be customer time or outbound tray). How can I exclude zeros in calculating average for the following expression or any other better idea for calculations

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

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

Sai_Mohan — Thu, 07 May 2020 19:33:31 GMT

Hi,

I have two columns like date and time seperately. i want to combine both columns as single in load script.

I am getting data from excel. please help me with this.

I have tried this two formula, but it wasn't working.

Timestamp(Date#([date],'DD-MM-YYYY') + Time#([time],'hh:mm:ss'), 'DD-MM-YYYY hh:mm:ss') AS DateTime,
Timestamp(Date#([date] & ' ' & [time], 'DD-MM-YYYY hh:mm:ss'), 'DD-MM-YYYY hh:mm:ss')AS Datenew,

Thanks

Sai