Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rczi
Contributor
Contributor

Number to timestamp

Hello ,

If i try to load a field into qlikview , lets name it MyField it gives a value as followed "44816,916666667" .

If i set this Field's number format to TimeStamp in the documents properties it shows as "2022.09.12. 22:00:00".

I would like to use this Field in an embedded if statement in the script as follows:

if(Hour(TimeStamp#(MyFieldname, 'h:mm:ss[.fff]'))=6 ,'1.hour', '2.hour) , so basically i'd like to get back the hour of the timestamp value, but the source format is not timestamp , but a basic integer (showed in the first sentence earlier).

Could any1 help with this problem with these presented informations?

Thank you all in advance!!

Labels (1)
2 Solutions

Accepted Solutions
Andrei_Cusnir
Specialist
Specialist

Hello,

 

If my understanding is correct, you have managed to convert "44816,916666667" value to  "2022.09.12. 22:00:00" value, by using the expression TimeStamp#(MyFieldname, 'h:mm:ss[.fff]')). Now you want to extract the HOURS part of the new value to use it within an If() statement. 

 

Note: You have specified that this is a Qlik Sense question but you mention QlikView in the description. I will share with you a solution to similar issue but for Qlik Sense since it is posted in Qlik Sense.

 

I believe it is easier to use the SubField() function and just extra the data that you want. For example the format that you have is YYYY.MM.DD h:mm:ss, which means that all you have to do is:

 

 SubField(SubField(EXPRESSION, ' ', 2), ':', 1)

 

EXPRESSION is the expression that gives you  "2022.09.12. 22:00:00" as output.

 

The inner SubField() will spilt the value to "2022.09.12." and "22:00:00" and then will return "22:00:00". After that the top level SubField() will split the value "22:00:00" to "22", "00" and "00" and return the value "22". So now you have the hours and you can use them in the If() statement.

 

 

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, addressed your concerns or at least pointed you in the right direction, please mark it as Accepted Solution to give further visibility to other community members. 
 

Help users find answers! Don't forget to mark a solution that worked for you! 🙂

View solution in original post

vinieme12
Champion III
Champion III

just  the below

 

=hour(timestamp(44816,916666667))

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

2 Replies
Andrei_Cusnir
Specialist
Specialist

Hello,

 

If my understanding is correct, you have managed to convert "44816,916666667" value to  "2022.09.12. 22:00:00" value, by using the expression TimeStamp#(MyFieldname, 'h:mm:ss[.fff]')). Now you want to extract the HOURS part of the new value to use it within an If() statement. 

 

Note: You have specified that this is a Qlik Sense question but you mention QlikView in the description. I will share with you a solution to similar issue but for Qlik Sense since it is posted in Qlik Sense.

 

I believe it is easier to use the SubField() function and just extra the data that you want. For example the format that you have is YYYY.MM.DD h:mm:ss, which means that all you have to do is:

 

 SubField(SubField(EXPRESSION, ' ', 2), ':', 1)

 

EXPRESSION is the expression that gives you  "2022.09.12. 22:00:00" as output.

 

The inner SubField() will spilt the value to "2022.09.12." and "22:00:00" and then will return "22:00:00". After that the top level SubField() will split the value "22:00:00" to "22", "00" and "00" and return the value "22". So now you have the hours and you can use them in the If() statement.

 

 

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, addressed your concerns or at least pointed you in the right direction, please mark it as Accepted Solution to give further visibility to other community members. 
 

Help users find answers! Don't forget to mark a solution that worked for you! 🙂
vinieme12
Champion III
Champion III

just  the below

 

=hour(timestamp(44816,916666667))

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.