Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
We have requirement to calculate time duration between two dates where we have 4 fields
Namely
From SAP
ERDAT(DD-MM-YYYY)
ERZET(HH-MM-SS)
UDATE(DD-MM-YYYY)
UTIME(HH-MM-SS)
We want to concatenate ERDAT & ERZET and UDATE & UTIME to make to date time fields and arrive hours between two dates
Already we used below functions but result is not getting due to date time format not detecting by qlikview as it is coming as text from source
Date( Timestamp#(ERDAT & ' ' &ERZET,'YYYY-MM-DD hh:mm:ss.fff') ,'DD-MM-YYYY hh:mm:ss')as DTKEY, Timestamp#(ERDAT & ' ' &ERZET,'YYYY-MM-DD hh:mm:ss.fff') as hours, Timestamp#(ERDAT &' '& ERZET,'DD-MM-YYYY hh:mm:ss[.fff]') as hour,
Appreciate your time .
Try
Timestamp(Timestamp#(ERDAT & ' ' &ERZET,'YYYY-MM-DD hh:mm:ss.fff') ,'DD-MM-YYYY hh:mm:ss') as DTKEY1,
Timestamp(Timestamp#(UDATE & ' ' &UTIME,'YYYY-MM-DD hh:mm:ss.fff') ,'DD-MM-YYYY hh:mm:ss') as DTKEY2,
Try
timestamp#(ERDAT &' '& replace(ERZET,'-',':') , 'DD-MM-YYYY hh:mm:ss)
The time-format delimiter within the expressions is ':' and in your SAP example its '-'. If those fields are already real dates and times you could use something like: timestamp(ERDAT + ERZET).
- Marcus
For hours difference
Load
Interval(
Timestamp(Timestamp#(ERDAT & ' ' &ERZET,'YYYY-MM-DD hh:mm:ss.fff') ,'DD-MM-YYYY hh:mm:ss') -
Timestamp(Timestamp#(UDATE & ' ' &UTIME,'YYYY-MM-DD hh:mm:ss.fff') ,'DD-MM-YYYY hh:mm:ss')
,'hh:mm') as Diff
From Source;
Tried this not working