How do I format a number as a String in a Load script

[debutler]

I have a load script that produces a values as the number of seconds

i.e. Total Seconds

I want to store this in my model as HH:MI:SS where it is always a 2 digit hour, month and seconds.

So 1 hours and 8 min and 3 seconds would should up as 01:08:03

Currently the below give me 1:8:3

Test_Table:
LOAD * INLINE [
     ID, Total Seconds
     1, 114
     2, 0
     3, NULL
     4, 5876
];



Test_Tab2:
load
      ID as %_ID,
      [Total Seconds],
      IsNull([Total Seconds]) as NullFlag,
      if(not IsNull([Total Seconds]),floor([Total Seconds]/3600) & ':'&
                                     floor(mod(round([Total Seconds]),3600)/60) & ':' &
                                     mod(mod(round([Total Seconds]),3600),60),'UNKNOWN') as [Total Time]
Resident Test_Table;

This give me the below but

%_ID	NullFlag	Total Seconds	Total Time
1	0	114	0:1:54
2	0	0	0:0:0
3	0		::
4	0	5876	1:37:56

What I want Is This:

%_ID	NullFlag	Total Seconds	Total Time
1	0	114	00:01:54
2	0	0	00:00:00
3	0		UNKNOWN
4	0	5876	01:37:56

AS a side note how do put NULL in an inline table?   I assume that is why my null check is failing.

[MarcoWedel · Accepted solution]

Use the Interval function instead, e.g. like this:

Interval([Total Seconds]/86400, 'hh:mm:ss') as [Total Time]

Regards

Marco

[MK_QSL]

Test_Table:

Load * Inline

[

  ID, Total Seconds

  1, 114

  2, 0

  3, NULL

  4, 5876

];

NoConcatenate

Test_Table2:

Load

  ID as %ID,

  [Total Seconds],

IF(NOT IsNum([Total Seconds]) or IsNull([Total Seconds]), 'UNKNOWN',

  Time#(NUM(FLOOR([Total Seconds]/3600),'00')&':'&NUM(FLOOR(MOD([Total Seconds],3600)/60),'00')&':'&NUM(MOD(MOD([Total Seconds],3600),60),'00'),'hh:mm:ss')) as [Total Time]

Resident Test_Table;

Drop Table Test_Table;

[debutler]

Thank you .... Very easy solution.