Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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
Yes but imagine i don't know what is the max length. I want to check all values and see which is the biggest
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;
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
RangeMax, exactly! Thank you
In this example was 11 characters but the number can be variable. The function i was looking is RangeMax. Thank you anyway
Best Regards