topic A simple question about number formatting in QlikView

A simple question about number formatting

Tue, 12 Oct 2010 21:51:29 GMT

I am trying to output the format of a table column to, eg. 8 days 3 hours 21 mins

In the Number Format box on the Properties >> Number tab, every time I type d, this displays the day.

In an Excel cell, it can be done by escaping reserved characters (e.g. you could use something like d \da\y\s to display '8 days' (because d, y and s, without the \ would display days, years, seconds, respectively).

Can formatting such as this be done in QV? It is easy to create customised formatting using non-reserved characters (e.g you can enter 'd bar' to output the value 8 as '8 bar'). Hope I'm making sense.

Thanks

James

A simple question about number formatting

johnw — Tue, 12 Oct 2010 23:26:30 GMT

Unfortunately, I don't think QlikView can handle escape characters like that. Instead, you need to build it up bit by bit. Like this, for instance (untested):

timestamp(MyField,'d') & ' days '
& timestamp(MyField,'h') & ' hours '
& timestamp(MyField,'m') & ' mins'

And if you need to preserve the underlying numeric value, such as for sorting, you can use a dual():

dual(timestamp(MyField,'d') & ' days '
& timestamp(MyField,'h') & ' hours '
& timestamp(MyField,'m') & ' mins'
,MyField)

It's not ideal, but I think it's all we have. (Edit: Well, not all we have. Plenty of other ugly ways to do it. Just no clean way to do it that I'm aware of.)

A simple question about number formatting

Wed, 13 Oct 2010 09:11:21 GMT

That is spot on John. Thank you very much!!!