topic Formatting interval with comma separtors in QlikView

Formatting interval with comma separtors

MrMadders — Thu, 30 May 2019 21:46:31 GMT

Hi there,

I have an expression which returns a very large number of hours and minutes e.g.

8124282:42

I want to format this like

8,124,282:42

Is there a way to achieve this with the interval format?

Re: Formatting interval with comma separtors

yogiachilleos — Thu, 30 May 2019 22:09:23 GMT

Howdy MrMadders,

You could try num(fieldName, ‘#,##0:00’) but not sure if that works. Alternatively you could script a solution as follows:
Load part1 & part2 as newFieldName;
Load
Num(Subfield(fieldName, ‘:’, 1), ‘#,##0’ as part1,
‘:’ & subfield(fieldName, ‘:’, 2) as part2
Resident tableName;

Kind regards,
Yogi Achilleos

Re: Formatting interval with comma separtors

rwunderlich — Fri, 31 May 2019 00:30:05 GMT

The interval format does not seem to accept a thousands sep. The workaround I can think of is to format as interval, reinterpret as number, and add the commas. I added a replace to get ':' before the minutes.

=replace(num(num#(interval(now(2) - MakeDate(2000,1,1), 'h.m')),'#,##0.00'),'.',':')

// returns 170,153:13

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com