Page 1 of 1
Generating Alphanumeric sequence using DS
Posted: Wed Aug 31, 2016 6:53 pm
by tan1111
Dear All,
I have a requirement of generating a alphanumeric sequence which looks like AA,AB...AZ,A0,A1...A9,BA...BZ,B0...B9...up to 99 where all the alphabets become 9.
So basically the 2nd character increases and when it becomes Z the number is reset to 0 and it goes on until 9, and then the 1st char increase one character and so on.
I hope I could explain the flow of the sequence. I need some ideas on how to implement such thing using Data stage as I am pretty new to the tool.
Thanks
Posted: Thu Sep 01, 2016 8:01 am
by FranklinE
Your sequence is what Excel uses to name columns, with the addition by you of the letter-numeral sequence between the end of one set and the beginning of the next.
I would question that addition. It breaks the pattern of the letter sequence.
Anyway, I don't know what algorithm Excel uses for its column headers, but it shouldn't be that difficult to work out a formula that represents base-26, meaning you have 26 "digits" to run through in each numeral position. I suppose it would then be possible to insert the letter-numeral section.
The simple, inelegant method is to create a table with the sequence, and read it. No extra coding other than to keep track of where you are in the table.
Posted: Thu Sep 01, 2016 8:44 am
by qt_ky
With the digits added, it goes to base 36. If you wanted a more programmatic approach, you could do some simple looping (i.e. from 1 to 72) and convert the integer to your custom sequence value by using some logic like Mod() function, If Then Else statements, and the Char() function. Sounds like a good morning brain warm up exercise.
Posted: Thu Sep 01, 2016 8:55 am
by FranklinE
qt_ky wrote:With the digits added, it goes to base 36.
Missed how the numerics extend the pattern. Good catch.
Posted: Thu Sep 01, 2016 8:58 am
by qt_ky
Thanks.
I suppose if the goal is only to build a 2-character wide ordered list, you would need to loop 1296 times, which would cover all possible values (36*36=1296).
Posted: Thu Sep 01, 2016 9:27 am
by UCDI
make an array of the digits you want.
make a sequence of base 10 values (0, 1, ... N)
you should be able to convert the base 10 into a string from there using either the change of base formula (logs) and some rounding to ensure integers or you can probably just do it with division & remainders (mod).
Basically you need to get one base 36 digit, look up the letter in your table, and cook up a string that way digit by digit, or do similar thing via a looping and tracking algorithm.
Posted: Thu Sep 01, 2016 11:58 pm
by SURA
I am not sure whether this will work for you or not, but still it may help for someone.
This solution is using Oracle, but you will get the same output. Then up to you to decide whether you need to use it or not!
Code: Select all
WITH data AS (SELECT Chr(65 + LEVEL - 1) str FROM dual CONNECT BY LEVEL <= 99) SELECT str ||column_value AS vVal FROM data, TABLE(Cast(MULTISET (SELECT LEVEL FROM dual CONNECT BY LEVEL < 99 + 1) AS sys.ODCINUMBERLIST))
where rownum < 2575
union all
SELECT vVal FROM (select chr(level + 65 - 1) pos1 from dual connect by level <= 26) MODEL DIMENSION BY (pos1 AS x, 'A' as y) MEASURES ('--' AS vVal) RULES UPSERT ALL ITERATE (26) ( vVal[any, chr(iteration_number + 65)] = (CV(x) || CV(y)));
Please test it before use.