Max length of a value

[brunopaulo]

Hi Community,

I'm struggling to find how to do the following example.

I have something like this

Name          |               Field A               |                Field B

A                                   29                                        2942                            

B                                   31                                        325         

C                                   41                                        4199999999

D                                   12                                        12222    

E                                   123                                        12356

So i want create a variable (in script) which allows me to check column (Field A) and column (Field B) to see the length of values and ill be able to get something like this

Name          |               Field A               |                Field B

A                                   29000000000                    29429999999                            

B                                   31000000000                    3259999999          

C                                   41000000000                      4199999999

D                                   12000000000                      12222999999    

E                                   12300000000                      12356999999

The length of both columns is given by the max length wich is 4199999999 (11 characters) and the column A should be filled with 0 until 11 characters and column B should be 11 characters of length filled with 9.

Thanks in advance

Best Regards

Bruno Paulo

[marcus_sommer]

I think you could use the following:

...

[Field A] & repeat('0', 11 - len([Field A])) as [Field A],

[Field B] & repeat('9', 11 - len([Field B])) as [Field B]

...

- Marcus

[brunopaulo]

Yes but imagine i don't know what is the max length. I want to check all values and see which is the biggest

[sunny_talwar · Accepted solution]

May be this

Table:

LOAD *,

RangeMax(Len([Field A]), Len([Field B])) as Len;

LOAD * INLINE [

    Name, Field A, Field B

    A, 29, 2942

    B, 31, 325

    C, 41, 4199999999

    D, 12, 12222

    E, 123, 12356

];

Left Join (Table)

LOAD Max(Len) as MaxLen

Resident Table;

FinalTable:

LOAD Name,

[Field A] & repeat('0', MaxLen - Len([Field A])) as [Field A],

[Field B] & repeat('9', MaxLen - Len([Field B])) as [Field B]

Resident Table;

DROP Table Table;

[marcus_sommer]

If I understand this right then is the max. length always 11 chars, or?

The length of both columns is given by the max length wich is 4199999999 (11 characters) and the column A should be filled with 0 until 11 characters and column B should be 11 characters of length filled with 9.

- Marcus

[brunopaulo]

RangeMax, exactly! Thank you

[brunopaulo]

In this example was 11 characters but the number can be variable. The function i was looking is RangeMax. Thank you anyway

Best Regards