Skip to main content
Announcements
Document boards are being consolidated, this board no longer allows NEW documents READ MORE

Translation from Numbers in Words

cancel
Showing results for 
Search instead for 
Did you mean: 
marcus_sommer

Translation from Numbers in Words

Last Update:

Sep 20, 2022 1:59:53 PM

Updated By:

Sue_Macaluso

Created date:

Jan 20, 2016 7:23:53 AM

Attachments

Inspired through this question: General Q. and the solution which tresesco provided in the sub-link: how to convert number into words I thought that must be (easier) possible with QlikView itself. At first I considered to split the number with a while-loop and catch to each single-number then a mapping-value. But because of the slight inhomogeneity from the spelling words to the numbers it would be some checking with if-loops necessary. Therefore I deceided to make it more simple. It needs only two small mapping-tables and a preceeding-load in which each word-part oft he number will be separate generated and afterwards simple string-concatenated and a little bit cleaned.

This example is restricted to numbers < 1M but the logic could be extended. I hope I have caught every possibility by the translation - but don't hesitate if you find a error or if you have ideas for improvements.  And this is the result:

Map_FirstNumbers:
mapping Load * Inline [
FirstNumbers, Text
1, one
2, two
3, three
4, four
5, five
6, six
7, seven
8, eight
9, nine
10, ten
11, eleven
12, twelve
13, thirteen
14, fourteen
15, fifteen
16, sixteen
17, seventeen
18, eighteen
19, nineteen
20, twenty
]
;

Map_SecondNumber:
mapping Load * Inline [
SecondNumber, Text
2, twenty
3, thirty
4, fourty
5, fifthy
6, sixty
7, seventy
8, eighty
9, ninety
]
;

NumbersInWords:
load *, capitalize(trim(replace(NumbersInWordsRawFormat, Repeat(Chr(32), 3), chr(32)))) as NumbersInWords;
load *, [6 value] & [6 unit] & [4+5 value] & [4-6 unit] & [3 value] & [3 unit] & [1+2 value] as NumbersInWordsRawFormat;
load *,
if(right(Number, 2) < 21, applymap('Map_FirstNumbers', right(Number, 2), chr(32)),
if(right(Number, 2) < 100, applymap('Map_SecondNumber', mid(right(Number, 2), 1, 1), chr(32)) & chr(32) &
applymap('Map_FirstNumbers', mid(right(Number, 2), 2, 1), chr(32)))) as [1+2 value],
if(LenNumber >= 3, applymap('Map_FirstNumbers', num#(mid(right(Number, 3), 1, 1)), chr(32)), chr(32)) as [3 value],
if(LenNumber >= 3 and num#(mid(right(Number, 3), 1, 1)) > 0, ' hundred ', chr(32)) as [3 unit],
if(match(LenNumber, 4, 5, 6),
if(LenNumber = 4, applymap('Map_FirstNumbers', num#(mid(right(Number, 4), 1, 1)), chr(32)),
if(num#(mid(right(Number, 5), 1, 2)) < 1, chr(32), if(num#(mid(right(Number, 5), 1, 2)) < 21,
applymap('Map_FirstNumbers', num#(mid(right(Number, 5), 1, 2))),
if(num#(mid(right(Number, 5), 1, 2)) < 100, applymap('Map_SecondNumber', num#(mid(right(Number, 5), 1, 1)), chr(32)) & chr(32) &
applymap('Map_FirstNumbers', num#(mid(right(Number, 4), 1, 1)), chr(32)))))), chr(32)) as [4+5 value],
if(LenNumber >= 5, ' thousand ', if(LenNumber = 4 and num#(mid(right(Number, 4), 1, 1)) > 0, ' thousand ', chr(32))) as [4-6 unit],
if(LenNumber = 6, applymap('Map_FirstNumbers', num#(mid(right(Number, 6), 1, 1)), chr(32)), chr(32)) as [6 value],
if(LenNumber >= 6 and num#(mid(right(Number, 6), 1, 1)) > 0, ' hundred ', chr(32)) as [6 unit];
load recno() as Number, len(recno()) as LenNumber Autogenerate 999999;

Marcus_Sommer

Labels (2)
Comments
settu_periasamy
Master III
Master III

Thank you for Sharing.

MK_QSL
MVP
MVP

TFS

0 Likes
Anonymous
Not applicable

Pretty slick.  Nice effort Marcus!

0 Likes
Not applicable

Cool stuff...

0 Likes
psankepalli
Partner - Creator III
Partner - Creator III

Very good artical

0 Likes
manojkulkarni
Partner - Specialist II
Partner - Specialist II

thanks for sharing

0 Likes
HirisH_V7
Master
Master

Very Nice.Thanks.

0 Likes
Anonymous
Not applicable

Wondeful !

0 Likes
Archana_R
Contributor
Contributor

@marcus_sommer  It's not working for more than 1 million.

Could anyone  share the logic which will work for more than 1 million.

Any help would be appreciated!
Thanks.


0 Likes
marcus_sommer

You need to extend the approach by increasing the number of records from 999999 to your your biggest number and then creating the appropriate fields for those numbers which are bigger as 1M. The last load-step before the final string is cleaned is:

load *, 
   [6 value] & [6 unit] & [4+5 value] & [4-6 unit] & [3 value] & [3 unit] & [1+2 value] 
as NumbersInWordsRawFormat;

and means that a number until 999999 is divided into 7 value + unit parts.

If your number is now bigger you need to create at least 2 fields more. The logic to create them should be the same as by the lower numbers - just checking the len() and the values for certain numbers/chars to apply the word millions respectively the appropriate mappings.

- Marcus

0 Likes
Version history
Last update:
‎2022-09-20 01:59 PM
Updated by: