so just as we like to refer to bugs as features, I'll label this craziness as creativity.
Why do I call this
* 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 22.214.171.124 servers to an 126.96.36.199 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)
2. Split the first character
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 188.8.131.52 servers. One difference when I did it on the 184.108.40.206 server though was that I joined the fields two at a time instead of all 3). This leaves us with 1 field again
6. Create a New field with text 0
7. Join the two fields
8. Split the first 3 characters. We will now ignore the final field which is possible in a load rule
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!
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.