Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Everybody,
very new o qlikview.
I wanted to get the time spend between colum1 and column2 but it return nothing i use the formula below
timestamp(timestamp#([Column1], 'd.hh:mm:ss') - timestamp#( [Column2], 'hh:mm:ss'),'hh:mm:ss'
) AS Total ,
Column1 | Column2 | Total |
2020-07-26 08:13:55.656 | 2020-07-26 08:13:55.858 | |
2020-07-26 08:59:11.212 | 2020-07-26 08:59:11.455 | |
2020-07-26 09:15:38.637 | 2020-07-26 09:15:38.930 | |
if I understood correctly in your formula you need to add .ff in Time format
you can try this :
Timestamp(((Timestamp#(Column2,'YYYY-MM-DD hh:mm:ss.fff')-Timestamp#(Column1,'YYYY-MM-DD hh:mm:ss.fff'))), 'hh:mm:ss.ff') as Total
One example:
Data:
LOAD *,
Timestamp(((Timestamp#(Column2,'YYYY-MM-DD hh:mm:ss.fff')-Timestamp#(Column1,'YYYY-MM-DD hh:mm:ss.fff'))), 'hh:mm:ss.ff') as Total
INLINE [
Column1, Column2
2020-07-26 08:13:55.656, 2020-07-26 08:13:55.858
2020-07-26 08:59:11.212, 2020-07-26 08:59:11.455
2020-07-26 09:15:38.637, 2020-07-26 09:15:38.930
];
output:
Thank you so much , this helps. Can i ask you why the formula i use is not returning anything apart from column -column1
if I understood correctly in your formula you need to add .ff in Time format
Hello Taoufiq,
thanks again , i try to get the year and month value also
Timestamp(((Timestamp#([Column2],'YYYY-MM-DD hh:mm:ss.fff')-Timestamp#([Column1],'YYYY-MM-DD hh:mm:ss.fff'))), ('YYYY-MM-DD hh:mm:ss.fff'))As total ,
it does return me the date value but it is giving me the date 1899-12-30 .It is something that i should fixed with the date value