Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
employe4_2
Contributor III

TimeStamp use

Hi,

in my database i got available my year day month hour minute second each in a differente field

i want to creat one field in this format TimestampFormat='MM/DD/YYYY hh:mm:ss TT';

first i tried to concat them  with proper separator, but at the end its just a string.

*(by the way, i had to convert them into numeric integer data so i did this : Num(Floor(clock_year_hmi), '0000') as year, and each time i used "year" in my concatenation, i had loading error  "year field not found" wich was defined right before  . If you can explain me why and how i could use variable names inside concat! would be nice)*

i try to use timestamp to make qlik understand that its a date and not some labelle or ID( so i can get a chronologique chart)

 LOAD*,
Timestamp(timestamp#(month&'/'&day&'/'&year&' '&hour&':'&minute&':'&second,'DD/MM/YYYY hh:mm:ss TT'))as DateStamp;

but this line give me only 12/30/1889 12:00:00 AM for every single one of my lines wich doesnt match my data

any suggestion ?

Capture.PNG

2 Solutions

Accepted Solutions
sunny_talwar

Why are you using Sum() function around DateStamp field? Use DateStamp in a filter object to see what you see....

Alternatively, you can try this

TimeStamp(MakeDate(year, month, day) + MakeTime(hour, minute, second) as DateStamp

But yet again, DO NOT use Sum() around a date and time field.

View solution in original post

marcus_sommer

Your format-code isn't correct - month and day are reversed, it includes TT which isn't available and maybe further things might not fit.

Beside this I wouldn't use the formatting-approach else:

makedate(year, month, day) as Date,
maketime(hour, minute, second) as Time

as separate fields and if you really need to show a timestamp you could use: timestamp(Date + Time).

- Marcus

View solution in original post

2 Replies
sunny_talwar

Why are you using Sum() function around DateStamp field? Use DateStamp in a filter object to see what you see....

Alternatively, you can try this

TimeStamp(MakeDate(year, month, day) + MakeTime(hour, minute, second) as DateStamp

But yet again, DO NOT use Sum() around a date and time field.

marcus_sommer

Your format-code isn't correct - month and day are reversed, it includes TT which isn't available and maybe further things might not fit.

Beside this I wouldn't use the formatting-approach else:

makedate(year, month, day) as Date,
maketime(hour, minute, second) as Time

as separate fields and if you really need to show a timestamp you could use: timestamp(Date + Time).

- Marcus