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
Generating Alphanumeric sequence using DS
Moderators: chulett, rschirm, roy
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.
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.
Franklin Evans
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson
Using mainframe data FAQ: viewtopic.php?t=143596 Using CFF FAQ: viewtopic.php?t=157872
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson
Using mainframe data FAQ: viewtopic.php?t=143596 Using CFF FAQ: viewtopic.php?t=157872
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.
Choose a job you love, and you will never have to work a day in your life. - Confucius
Missed how the numerics extend the pattern. Good catch.qt_ky wrote:With the digits added, it goes to base 36.
Franklin Evans
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson
Using mainframe data FAQ: viewtopic.php?t=143596 Using CFF FAQ: viewtopic.php?t=157872
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson
Using mainframe data FAQ: viewtopic.php?t=143596 Using CFF FAQ: viewtopic.php?t=157872
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.
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.
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!
Please test it before use.
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)));
Thanks
Ram
----------------------------------
Revealing your ignorance is fine, because you get a chance to learn.
Ram
----------------------------------
Revealing your ignorance is fine, because you get a chance to learn.