numeric value into words

[#1]

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?

[Miguel_Angel_Baeyens]

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

[#3]

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.....?

[nstefaniuk · Accepted solution]

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.

[#5]

nstefaniuk

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