topic Re: How do I format a number as a String in a Load script in QlikView

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

debutler — Wed, 23 Apr 2014 18:45:02 GMT

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.

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

MarcoWedel — Wed, 23 Apr 2014 19:20:00 GMT

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

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

Regards

Marco

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

MK_QSL — Wed, 23 Apr 2014 19:24:54 GMT

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;

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

debutler — Wed, 23 Apr 2014 19:31:37 GMT

Thank you .... Very easy solution.