Translation from Numbers in Words

    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