topic numeric value into words in QlikView

numeric value into words

Tue, 10 Jan 2012 09:06:21 GMT

Dear all

can i show my numeric figure (i.e Amount into words) like 10 to ten 1000 to one thousand and so on........ is there any way?

Re: numeric value into words

Miguel_Angel_Baeyens — Tue, 10 Jan 2012 10:24:46 GMT

Hi Shariq,

Depending on the level of customization you need you can use an inline mapping table:

TextMap:
MAPPING LOAD * INLINE [
Number, Text
1, One
10, Ten
100, One Hundred
1000, One Thousand
];
Number:
LOAD Number,
     ApplyMap('TextMap', Number) AS NumberToText
FROM...

Hope that helps.

Miguel

numeric value into words

Mon, 16 Jan 2012 03:58:00 GMT

Thanks for reply Miguel ,

That is helpful but it will not work for the figure like $1264 mean "Twelve hundred sixty four dollars" b/c it is not dynamic

I also have the SQL script having a function which convert number to text how can I use this script in Qlikview...

any thing through macros or.....?

Re: numeric value into words

nstefaniuk — Mon, 16 Jan 2012 09:46:16 GMT

Hi.

If you know the min/max possible amounts you can pre-load the words with a SQL query like that (in Oracle, for example for amount from 1 to 1000) :

select
"amount",
'yourfunction("amount")' "words"
from
(
select
level "amount"
from
dual
connect by level < 1000
)

The best could be to load the words 1 time in 1 shot and keep it in a QVD, and use it in read only. Care this query, if not used properly, can go in infinite loop.

If you have a too huge range but few possibilities, maybe you can create a loop on the possible amounts to retrieve the words from SQL. It means that if you have 5000 distinct amounts, you will send 5000 SQL queries.

Last solution is to translate the function you have in qlikview script or VB script.

numeric value into words

Tue, 17 Jan 2012 07:40:53 GMT

nstefaniuk

THANKS ALOT..............I DID IT