creating TimeStampFormat

[employe4_2]

Hi,

i'm looking for creating my Time axis with default time syntax:

In my database i have at my disposal , a variable for the day, an other one for the month and so forth

and i concat them to have this format

i got 3 problemes:

• the numbers for my month, day , years, hours, minuts, seconds are with a coma, i suppose it means its numéeric but i tried to convert them into string with text function but they keep that coma
• What does the" [.fff] TT" at the end of the format stand for ?
• and even if the concatenation goes well, how do i make qlik recognize its a calendar thing? Is it automatic once it's working ?

Thanks in advance for the help

[rubenmarin1]

Hi,

1- Try using round() or floor() to retrieve the integer value of each variable
2- fff are the miliseconds, and TT shows AM/PM
3- Timestamp(TimeStamp#(FieldValue, 'Format')) -> timestamp#() tells the format of FieldValue, the outside Timestamp() transforms it in your document format

[employe4_2]

Hi thanks for the advice,

indee the floor  function allow me to get the integer value i want.

1: But as i try to concat, the string become long so i have to give it a name :wholç_string  as Datetime_EXT ( at the end of the really long line you can't see.) but as i load, "ERROR Field Datetime_EXT not found" same for the TimestampFormat wich is set in the data load script : "ERROR Field TimestampFormat not found"

i guess its not the way it works

2: even tho i put the whole string ( 7th line) in the   Timestamp(Timestamp#( whole_String))  without specify the format like in this exemple ( https://help.qlik.com/en-US/sense/November2018/Subsystems/Hub/Content/Sense_Hub/Scripting/Interpreta...), it return nothing to me

The good point, is : my whole string  returne me a string which looks like the good format, but ain't recognized  as calendar data by qlik

[rubenmarin1]

Hi, it can be something like this:

Table:

// this is a preceding load, uses the calculated fields of the LOAD below

LOAD *,

  timestamp(timestamp#(day &'/'& month &'/'& year..., 'DD/MM/YYYY hh:mm:ss TT'))

;

LOAD Num(Floor(...-col1), '00') as day,

  Num(Floor(...col2),'00') as month,

  Num(Floor(...col3),'0000') as year

...

[employe4_2]

this is the only configuration of code without errors (field not found), if i try to  interchange one of the LOAD. it give me the field not found

 it doesnt give me anymore errors but still one more step!

 the sum(timestamp) display something irrelevant..., alone:  display nothing

i might block here ...i dont understand why he deosnt take value of day , month year...

[rubenmarin1]

Hi, you have timestamp#(day,...month but the format is DD/MM..., fortmat should be the one you are creating with the fields, in this case: MM/DD/YYYY hh:mm:ss.

you can try with fixed values before replacing them with fields:
=Timestamp(Timestamp#('06/25/2018 12:30:05', 'MM/DD/YYYY hh:mm:ss'))