Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)')
+
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)
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