Creative Load Rule #1 - Left Pad a Field with 0

OK I really wanted to call this Stupid Load Rules but that would be stealing someone else's catch phrase,
so just as we like to refer to bugs as features, I'll label this craziness as creativity.

Why do I call this crazy creative? Because I would never ever do this in a load rule, I'd do it in the data source or during the extraction. But another thing I'll never* do is pass up a challenge!

* Almost never. Can't seem to make this fine print any smaller!

So the challenge? The below was posted on the Essbase OTN forum:

I need to add a 0 to the left if the data is less than 3 digits. Is this possible to do it in a data load rule.

e.g. 99 , change it to 099. Some values will have 3 digits , there should be no change to those.

Believe it or not I actually killed two EAS servers with this load rule and I had barely done anything. Seems like an easily repeatable bug in EAS. Once I moved from 11.1.2.1 servers to an 11.1.2.2 server however I was able to proceed. It only took about 15 field edits and wasn't that much effort once the theory was devised.

So here was my theory:

1. Start with a text file with two rows (or more if you like)

32

469

2. Split the first character

3 2
4 69

3. Split the first character on field2

3 2 _
4 6 9

4. Reverse the order of the fields

_ 2 3
9 6 4

5. Join the fields (this is the step that crashed 2 EAS 11.1.2.1 servers. One difference when I did it on the 11.1.2.2 server though was that I joined the fields two at a time instead of all 3). This leaves us with 1 field again

23
964

6. Create a New field with text 0

23 0
964 0

7. Join the two fields
230
9640

8. Split the first 3 characters. We will now ignore the final field which is possible in a load rule

230 _
964 0

9. Split the fields again like in steps 1 and 2
2 3 0 _
9 6 4 0

10. Reverse the order of the fields (hold together EAS!)

0 3 2 _
4 6 9 0

11. Join them back together!

032 _
469 0

We now have what we wanted. Numbers with a left pad of 0. I didn't really consider single digit numbers but then the poster didn't specifically mention that and I know it could be done if I've done it with 2.

Here is the proof (may not match above steps exactly but it should look similar to this).

It certainly doesn't beat the 5 seconds it would take to do in SQL but it was definately more fun! Unfortunately I don't have access to restart those two EAS servers until the morning.

Happy Essbase-ing!