Elegant way to convert multiple Date fields

[JanTraeg]

Hello experts! 

I just switched to Qlick, coming from Power BI. 

As I am getting the hang of things I am trying to learn not only how to get things done, but how to get them done the right way. 

I am currently working with a table which is using Date-Fields that are formatted: YYYYMMDDHHMM. I formatted said fields using the follwoing function within a load-statement. Let's say in this example the fields name is "E_Date"

Timestamp#(MakeDate(Left([E_Date],4),Mid([E_Date],5,2), Mid([E_Date],7,2))& ' ' & MakeTime(mid([E_Date],9,2),Mid([E_Date],11,2),00)) as [Example_Date]

This function is working fine, but I have to copy and paste it for every field and replace the field name. 

I tried writing a subroutine to make things more practical, but then learned that Call-Statements can not be used within Load-Statements. 

See the subroutine I tried below: 

sub ConvertDT (a,b)
//Possible format for 'a': YYYYMMDD, HHMM, YYYYDDTTHHMM
//Parameters: a= String that is to be formatted
// b= Pursued Format d=date, t=Time, dt=Timestamp

//Checking String length and form, if unsuitable returns '#WRONG#' 
IF (len(a) <> 8 AND b = 'd') OR (len(a) <> 4 AND b = 't') OR (len(a) <>12 AND b = 'dt') then
LET a='#WRONG#';
exit sub
end IF

//Konversion
IF b = 'd' then
LET a = MakeDate(left(a,4),mid(a,5,2),right(a,2));
ELSEIF b = 't' then
LET a = MakeTime(left(a,2),right(a,2);
ELSE
LET a = Timestamp(Timestamp#(a, 'YYYYMMDDhhmm'),'DD.MM.YYYY hh:mm');
end IF
end sub;

I was hoping this way I could make date-conversion a lot easier. 

Do you have any suggestions on how to to this another way? Maybe using Macros or Variables? 

Best regards and thanks in advance, 

Jan

[Or]

Timestamp#(Fieldname, 'YYYYMMDDhhmm') should work for a conversion.

timestamp(Timestamp#(Fieldname, 'YYYYMMDDhhmm')) will also format this as a timestamp based on your default format.

Note that this is case sensitive (M is months, m is minutes)

[Øystein_Kolsrud · Accepted solution]

As @Or points out, the functions "Timestamp" and "Timestamp#" are typically what you would use for this type of operations. Here is a short thread on the topic with some useful links:

https://community.qlik.com/t5/App-Development/Diff-b-w-Timestamp-and-Timestamp/td-p/1458861

Short summary is that "Timestamp#" reads a string and creates a timestamp according to the format, and "Timestamp" is used to specify what the string representation of a date should be. In your case you can do this to read your date string:

Timestamp#(E_Date, 'YYYYMMDDhhmm') as [Example_Date]

If you add such as field as an expression to a table for instance, then you can control the formatting of the date expression in the expression settings. If you want to add it as a dimension and you want to specify the formatting on the load script then you can wrap the field definition with the "Timestamp" function to choose your formatting. For instance like this:

Timestamp(Timestamp#(E_Date, 'YYYYMMDDhhmm'), 'YYYY MMM DD hh:mm') as [Example_Date]

This will give you values of the format '2023 Aug 28 11:03'.

If you have many fields like this and you want to avoid copy/pasting this string over and over and over again, then one option is to add your timestamp definition in a variable and use variable expansion. Something like this:

Set LogTimestamp = Timestamp(Timestamp#($1, 'YYYYMMDDhhmm'), 'YYYY MMM DD hh:mm');

Then you can use that variable in the load statement like this where you provide the field as argument:

$(LogTimestamp(E_Date)) as [Example_Date]

[JanTraeg]

Thanks @Or  and @Øystein_Kolsrud !

@Øystein_Kolsrud I accepted you Reply as the Solution since this is what I was looking for. Because I can imagine use cases in which I will have more complicated functions that I would want to reuse. 

So, if I understand correctly: 

Set LogTimestamp = Timestamp(Timestamp#($1, 'YYYYMMDDhhmm'), 'YYYY MMM DD hh:mm');

Defines the Variable "LogTimestamp" as the function Timestamp(Timestamp#(Parameter1, Format of Source), pursued format)

To then use ist, I call $(LogTimestamp(E_Date)) 
In using "$" I tell Qlik to execute the previous defined variable, whilst letting it know to replace $1 with "E_Date".'
Correct? 

I guess when I am using SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]'; at the beginning of my script, I won't need the 'YYYY MM DD hh:mm'

Br, 

Jan

[Øystein_Kolsrud]

The use of that "$" doesn't really tell it to execute anything, it just expands the variable, and then it all gets executed. Like a macro. You can read more about dollar-sign expansion in Qlik Sense here:

https://help.qlik.com/en-US/sense/May2023/Subsystems/Hub/Content/Sense_Hub/Scripting/dollar-sign-exp...

And in particular you can read about the use of parameters for such expansions here:

https://help.qlik.com/en-US/sense/May2023/Subsystems/Hub/Content/Sense_Hub/Scripting/dollar-sign-exp...

[JanTraeg]

Using this information I rewrote my initial function: 

Let d= 'YYYYMMMDD';

Let t= 'hhmm';

Let dt = 'YYYYMMDDhhmm';

Set CAT = If((len($1) <> 8 AND $2 = 'd') OR (len($1) <> 4 AND $2 = 't') OR (len($1) <>12 AND $2 = 'dt'), '#WRONG#',     //Checking String length and form, if unsuitable returns '#WRONG#' 

If($2 = 'd', MakeDate(left($1,4),mid($1,5,2),right($1,2)), 

If($2 = 't', MakeTime(left($1,2),right($1,2)), 
If($2 = 'dt', Timestamp(Timestamp#($1,'YYYYMMDDhhmm')), 

'#VERYWRONG'))));

I "Call" this using: 

$(CAT([E_Date], 'dt')) as Example_Date,

This works just as intended 🙂