Number to timestamp

[rczi]

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!!

[Andrei_Cusnir · Accepted solution]

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. 
 

[vinieme12 · Accepted solution]

just  the below

=hour(timestamp(44816,916666667))