Exercise 1: How to Convert Char Code to Number Code

Let's Chat: I am creating a new label Exercise. In this section we will discuss the solution for all basic question. I don't feel that these questions should be treated like scenario because these questions are too basic and we are asking these type of questions in interview to judge candidate level and authenticity :-)
Just keep walking and one day you will be in my seat :-)

Exercise 1: How to Convert Char Code to Number Code

( Source)
CODE
----------
ABCD
BCDE
EFGH

(Target)
CODE
-------
1234
2345
5678

Analysis/Theory
After analyzing target we can conclude that number which are appearing in target those are serial number starting from 1 for letter A, so 26 would be for Z. we already know only one type of number we can get from letters that is ASCII and we don't see that value here so, we have to achieve this manually.
We have to get the number according to each and every letter... (Ok got it we can achieve this through single Expression Transformation only ... Let's do it Practically:

Practically:

Step 1: 
Expression
Create 10 ports (Columns)

FIRST_LETTER (Variable Port)= UPPER(SUBSTR(CODE,1,1))
SECOND_LETTER (Variable Port)= UPPER(SUBSTR(CODE,2,1))
THIRD_LETTER (Variable Port)= UPPER(SUBSTR(CODE,3,1))
FOURTH_LETTER (Variable Port)= UPPER(SUBSTR(CODE,4))

NUMBER_1 (Variable Port)= IIF(FIRST_LETTER='A', 1, IIF(FIRST_LETTER='B', 2, IIF(FIRST_LETTER='C', 3, IIF(FIRST_LETTER='D', 4,IIF(FIRST_LETTER='E', 5, IIF(FIRST_LETTER='F', 6,IIF(FIRST_LETTER='G', 7, IIF(FIRST_LETTER='H', 8, IIF(FIRST_LETTER='I', 9, IIF(FIRST_LETTER='J', 10, IIF(FIRST_LETTER='K', 11, IIF(FIRST_LETTER='L', 12, IIF(FIRST_LETTER='M', 13, IIF(FIRST_LETTER='N', 14, IIF(FIRST_LETTER='O', 15, IIF(FIRST_LETTER='P', 16, IIF(FIRST_LETTER='Q', 17, IIF(FIRST_LETTER='R', 18, IIF(FIRST_LETTER='S', 19, IIF(FIRST_LETTER='T', 20,IIF (FIRST_LETTER='U', 21, IIF(FIRST_LETTER='V', 22, IIF(FIRST_LETTER='W', 23, IIF(FIRST_LETTER='X', 24, IIF(FIRST_LETTER='Y', 25, IIF(FIRST_LETTER='Z', 26))))))) )))))))))))))))))))

NUMBER_2 (Variable Port)= IIF(SECOND_LETTER='A', 1, IIF(SECOND_LETTER='B', 2, IIF(SECOND_LETTER='C', 3, IIF(SECOND_LETTER='D', 4,IIF(SECOND_LETTER='E', 5, IIF(SECOND_LETTER='F', 6,IIF(SECOND_LETTER='G', 7, IIF(SECOND_LETTER='H', 8, IIF(SECOND_LETTER='I', 9, IIF(SECOND_LETTER='J', 10, IIF(SECOND_LETTER='K', 11, IIF(SECOND_LETTER='L', 12, IIF(SECOND_LETTER='M', 13, IIF(SECOND_LETTER='N', 14, IIF(SECOND_LETTER='O', 15, IIF(SECOND_LETTER='P', 16, IIF(SECOND_LETTER='Q', 17, IIF(SECOND_LETTER='R', 18, IIF(SECOND_LETTER='S', 19, IIF(SECOND_LETTER='T', 20,IIF (SECOND_LETTER='U', 21, IIF(SECOND_LETTER='V', 22, IIF(SECOND_LETTER='W', 23, IIF(SECOND_LETTER='X', 24, IIF(SECOND_LETTER='Y', 25, IIF(SECOND_LETTER='Z', 26))))))) )))))))))))))))))))

NUMBER_3 (Variable Port)= IIF(THIRD_LETTER='A', 1, IIF(THIRD_LETTER='B', 2, IIF(THIRD_LETTER='C', 3, IIF(THIRD_LETTER='D', 4,IIF(THIRD_LETTER='E', 5, IIF(THIRD_LETTER='F', 6,IIF(THIRD_LETTER='G', 7, IIF(THIRD_LETTER='H', 8, IIF(THIRD_LETTER='I', 9, IIF(THIRD_LETTER='J', 10, IIF(THIRD_LETTER='K', 11, IIF(THIRD_LETTER='L', 12, IIF(THIRD_LETTER='M', 13, IIF(THIRD_LETTER='N', 14, IIF(THIRD_LETTER='O', 15, IIF(THIRD_LETTER='P', 16, IIF(THIRD_LETTER='Q', 17, IIF(THIRD_LETTER='R', 18, IIF(THIRD_LETTER='S', 19, IIF(THIRD_LETTER='T', 20,IIF (THIRD_LETTER='U', 21, IIF(THIRD_LETTER='V', 22, IIF(THIRD_LETTER='W', 23, IIF(THIRD_LETTER='X', 24, IIF(THIRD_LETTER='Y', 25, IIF(THIRD_LETTER='Z', 26))))))) )))))))))))))))))))

NUMBER_4 (Variable Port)= IIF(FOURTH_LETTER='A', 1, IIF(FOURTH_LETTER='B', 2, IIF(FOURTH_LETTER='C', 3, IIF(FOURTH_LETTER='D', 4,IIF(FOURTH_LETTER='E', 5, IIF(FOURTH_LETTER='F', 6,IIF(FOURTH_LETTER='G', 7, IIF(FOURTH_LETTER='H', 8, IIF(FOURTH_LETTER='I', 9, IIF(FOURTH_LETTER='J', 10, IIF(FOURTH_LETTER='K', 11, IIF(FOURTH_LETTER='L', 12, IIF(FOURTH_LETTER='M', 13, IIF(FOURTH_LETTER='N', 14, IIF(FOURTH_LETTER='O', 15, IIF(FOURTH_LETTER='P', 16, IIF(FOURTH_LETTER='Q', 17, IIF(FOURTH_LETTER='R', 18, IIF(FOURTH_LETTER='S', 19, IIF(FOURTH_LETTER='T', 20,IIF (FOURTH_LETTER='U', 21, IIF(FOURTH_LETTER='V', 22, IIF(FOURTH_LETTER='W', 23, IIF(FOURTH_LETTER='X', 24, IIF(FOURTH_LETTER='Y', 25, IIF(FOURTH_LETTER='Z', 26))))))) )))))))))))))))))))

NUMERIC_CODE (Variable Port)= NUMBER_1||NUMBER_2||NUMBER_3||NUMBER_4

O_NUMERIC_CODE (Output Port)= TO_INTEGER(NUMERIC_CODE)

Step 3: Connect O_NUMERIC_CODE to Target

BINGO!!!

***LIMITATION: Solution above will work only for maximum code length four only.

Another Approach

If, we can create one table having letters and there number then we will escape from writing this much of code. Simply do lookup on that table and get the ID of that letter... In this approach we need few more transformation and this I am leaving upon you.. do some home work and Find Out  :-)

Feel free to post your doubt in comment section.

Due to limitation, I can't attach exported .xml file which you can Import in your repository and see or analyse. (Feel free to mail me if you need .xml file with Scenario number and Question.)

If you like the approach and solution feel free to share with others.

Thanks.

hpandey.harsh@gmail.com