Extract each character from an element

Post Reply
ViRa
Regular Participant
Posts: 155
Joined: Tue May 14, 2013 1:53 pm
OLAP Product: Cognos BI, TM1
Version: 9.5.2 - 10.1.1
Excel Version: Excel 2003

Extract each character from an element

Post by ViRa »

Hi all,

In the measure dimension, I've an element called 'Group ID' which is made up of 5 characters. The requirement is to display this Group ID in a scrambled format for security purposes. There is a specific code for replacing each of the character in order to produce the output in scrambled format. Eg, If Group ID is 'abcde' then as per the code 'a' should be replaced with 'C', 'b' should be replaced with 'T' etc.

I would like to know if it is possible to extract each character from an element's value so I can write a While loop to fetch and replace each character.

Please let me know. Thanks for your time and help.
kangkc
Community Contributor
Posts: 206
Joined: Fri Oct 17, 2008 2:40 am
OLAP Product: TM1, PA , TMVGate
Version: 2.x
Excel Version: 36x
Location: Singapore
Contact:

Re: Extract each character from an element

Post by kangkc »

If you can get the length of element, and if you can get the sub-string of the element. You will be able to achieve what you want.
Both functions are available in TI.
TrevorGoss
Community Contributor
Posts: 217
Joined: Thu Aug 15, 2013 9:05 am
OLAP Product: TM1
Version: 10.2.1.1
Excel Version: 14.0.6129.5000

Re: Extract each character from an element

Post by TrevorGoss »

Try this..

Code: Select all


vGroupID = 'abcde';

vScrambled = '';

vLenOfGroupID = LONG(vGroupID);

x = 1;

While(x <= vLenOfGroupID);

             vChar = SUBST(vGroupID,x,1);

             If(vChar @='a');

                      vChar = 'C';
                      vScrambled = vScrambled | vChar;
            #Else if..... b c d e.....
             ## Do not forget counters in while loops - infinite loop will occur if you do!
             x = x + 1;

End;


There are other functions that can help you here, SCAN finds a index number for a string inside a string, INSRT will insert a string into a string.

My code is getting the original string, looping around it and depending on the Char (such as a) it will replace it with the correct char. It will then attach thw new char to the scrambled string.
tm1_bloke
Posts: 25
Joined: Sun Oct 13, 2013 6:03 am
OLAP Product: TM1
Version: 10.2
Excel Version: 2010

Re: Extract each character from an element

Post by tm1_bloke »

Technical solution would be really simple - just string manipulation with couple of TI functions. Two further questions came to my mind. How are you going to maintain the data integrity if the elements in your data source are not corresponding the ones in TM1? And what is the value of having scrambled non-sense codes in TM1? If end-users don't recognise the elements, it would be the same to aggregate everything into one element.
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Extract each character from an element

Post by tomok »

tm1_bloke wrote:How are you going to maintain the data integrity if the elements in your data source are not corresponding the ones in TM1? And what is the value of having scrambled non-sense codes in TM1? If end-users don't recognise the elements
My thoughts exactly. This really makes no sense. What is the point of showing scrambled data to someone? If the user is not going to know what it means then why show it at all? That's the easy answer. Just give them security rights of NONE to the measure and problem solved.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
ViRa
Regular Participant
Posts: 155
Joined: Tue May 14, 2013 1:53 pm
OLAP Product: Cognos BI, TM1
Version: 9.5.2 - 10.1.1
Excel Version: Excel 2003

Re: Extract each character from an element

Post by ViRa »

Thanks Trevor Goss for your guidance. Yes, I'm currently using DELET and INSRT functions within a While loop. I will incorporate your suggestion and will update the post with my results.

Thanks Tom Ok and TM1_Bloke for your replies. The reason some of the elements need to be scrambled is because this is a cube that stores sensitive diagnosis of the members and the cube data is shared across various account holders. The requirements asks for this and they are ok with not being able to see such member's actual data.
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Extract each character from an element

Post by tomok »

ViRa wrote:Thanks Tom Ok and TM1_Bloke for your replies. The reason some of the elements need to be scrambled is because this is a cube that stores sensitive diagnosis of the members and the cube data is shared across various account holders. The requirements asks for this and they are ok with not being able to see such member's actual data.
This is a perfect example of why they incorporated security capabilities into TM1. Are the business owners aware that all you had to do was implement security on the Measures dimension and only grant privileges to the "secret" measure to those people that should be allowed to access it? There is no need to scramble anything, just take it away from those that shouldn't see it. How are you going to control who see's the scrambled data versus those who see's the real data? Security? Right. Instead of showing those without access some garbage information, just don't show anything at all.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
BariAbdul
Regular Participant
Posts: 424
Joined: Sat Mar 10, 2012 1:03 pm
OLAP Product: IBM TM1, Planning Analytics, P
Version: PAW 2.0.8
Excel Version: 2019

Re: Extract each character from an element

Post by BariAbdul »

Thanks Tomok,I couldn't agree more with you.
"You Never Fail Until You Stop Trying......"
ViRa
Regular Participant
Posts: 155
Joined: Tue May 14, 2013 1:53 pm
OLAP Product: Cognos BI, TM1
Version: 9.5.2 - 10.1.1
Excel Version: Excel 2003

Re: Extract each character from an element

Post by ViRa »

I agree Tom, but the requirement specifically wants to see the data in scrambled format. The process of displaying the data this way is being fulfilled by another system currently and I've been asked to emulate the same setup using TM1. I have no other choice but to display scrambled data.
rmackenzie
MVP
Posts: 733
Joined: Wed May 14, 2008 11:06 pm

Re: Extract each character from an element

Post by rmackenzie »

ViRa wrote:... the requirement specifically wants to see the data in scrambled format. The process of displaying the data this way is being fulfilled by another system currently and I've been asked to emulate the same setup using TM1. I have no other choice but to display scrambled data.
I would suggest a Vignere cipher - it's just a couple of one-liners for scrambling and unscrambling:

Code: Select all

# encryption parameters
# must be upper case, no spaces
p = 'YOURSECRETELEMENTNAME';
k = 'DAVYJONES';
c = '';
d = '';

# encipher
n=1;WHILE(n<=LONG(p));c=c|CHAR(65+(MOD(CODE(p,n)+CODE(k,MOD(n,LONG(k))+1),26)));n=n+1;END;

# decipher
n=1;WHILE(n<=LONG(c));d=d|CHAR(65+(MOD(CODE(c,n)+26-CODE(k,MOD(n,LONG(k))+1),26)));n=n+1;END;

# output
DatasourceASCIIQuoteCharacter = '';
AsciiOutput ( 'output.txt', 'Input: ' | p );
AsciiOutput ( 'output.txt', 'Encipher: ' | c );
AsciiOutput ( 'output.txt', 'Decipher: ' | d );
As a single secret key will both encipher and decipher, it will be reasonably easy to descramble the element names in the future if your requirements (hopefully) change.
Robin Mackenzie
ViRa
Regular Participant
Posts: 155
Joined: Tue May 14, 2013 1:53 pm
OLAP Product: Cognos BI, TM1
Version: 9.5.2 - 10.1.1
Excel Version: Excel 2003

Re: Extract each character from an element

Post by ViRa »

Thanks rmackezie for your guidance. They have given me a predefined code for each character and I need to use those. Thanks for showing me an alternate way though.

Since I need to replace each character, I'm using 'DELET' and 'INSRT' functions within CellPutS. I plan to delete the character and insert the new one. However, I'm stuck at a syntax error at CellPutS relating to a missing parenthesis. Could somebody please guide me. The code looks like this

Code: Select all

vGroupID = CellGetS('Cube_Name',e1,e2,e3,...'GRP_ID');

vLenOfGroupID = LONG(vGroupID);

x = 1;

While(x <= vLenOfGroupID);

             vChar = SUBST(vGroupID,x,1);

             If(vChar @='a');

CellPutS((DELET(vGroupID,x,1(INSRT('C',vGroupID,x)))),'W_Cube',vIncurred_Dt_Yr_Mth,MEDSRV_KEY,vProd_Level3_Cd,RISK_TYPE2_CD,vTos_Cat2_Cd,vAcct_Num,'GRP_ID');
Endif;
             x = x + 1;
End;
Thanks for your time.
Wim Gielis
MVP
Posts: 3103
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Extract each character from an element

Post by Wim Gielis »

Code: Select all

CellPutS( INSRT('C', DELET(vGroupID, x, 1), x), ...
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
ViRa
Regular Participant
Posts: 155
Joined: Tue May 14, 2013 1:53 pm
OLAP Product: Cognos BI, TM1
Version: 9.5.2 - 10.1.1
Excel Version: Excel 2003

Re: Extract each character from an element

Post by ViRa »

Thanks Wim. I was able to resolve the syntax error in CellPutS with the correction provided by you.

However, whenever I run the TI process, it seems the 'While' loop is not getting executed. That is, when I run the TI process for the first time, the 'ElseIf' condition gets executed and only one character is replaced. I've to execute the TI for the second time for another 'ElseIf' to execute and character to be replaced. Then third time for another condition to be executed and so on. I tried changing the code a couple of ways but could not resolve the issue. Could somebody please assist me with the While loop not being executed correctly? Appreciate your time and help. The code now looks like this -

Code: Select all

vGroupID = CellGetS('Cube_Name',e1, e2, e3...,'GRP_ID');

vLenOfGroupID = LONG(vGroupID);

x = 1;

While(x <= vLenOfGroupID);

vChar = SUBST(vGroupID,x,1);

If(vChar @='a');
CellPutS(INSRT('C', DELET(vGroupID, x, 1), x),'Cube_Name',e1, e2, e3...,'GRP_ID');

ElseIf(vChar @='b');
CellPutS(INSRT('E', DELET(vGroupID, x, 1), x),'Cube_Name',e1, e2, e3...,'GRP_ID');

ElseIf(vChar @='c');
CellPutS(INSRT('F', DELET(vGroupID, x, 1), x),'Cube_Name',e1, e2, e3...,'GRP_ID';

Endif;
x = x + 1;

End;

Thanks for your time and help.
ViRa
Regular Participant
Posts: 155
Joined: Tue May 14, 2013 1:53 pm
OLAP Product: Cognos BI, TM1
Version: 9.5.2 - 10.1.1
Excel Version: Excel 2003

Re: Extract each character from an element

Post by ViRa »

Can somebody please assist me with why I've to run the TI process for each of the If condition to be executed? This will greatly help me move ahead. Thanks for your time and help.
rmackenzie
MVP
Posts: 733
Joined: Wed May 14, 2008 11:06 pm

Re: Extract each character from an element

Post by rmackenzie »

So your code table is this:
abcdefghijklmnopqrstuvwxyz
CDEFGHIJKLMNOPQRSTUVWXYZAB
And, for any input character with an ASCII value of x, then it's scrambled character will be the ASCII character given by x-30.

Therefore your TI should be:

Code: Select all

vGroupID = CellGetS('Cube_Name',e1, e2, e3...,'GRP_ID');
vLenOfGroupID = LONG(vGroupID);
vScrambledGroupId = '';
x = 1;
While(x <= vLenOfGroupID);
  nASCIIForChar = CODE ( vGroupID, x );
  vScrambledGroupId = vScrambledGroupId | CHAR ( nASCIIForChar - 30 );
  x = x + 1;
End;
CellPutS(vScrambledGroupId ,'Cube_Name',e1, e2, e3...,'GRP_ID');
Robin Mackenzie
ViRa
Regular Participant
Posts: 155
Joined: Tue May 14, 2013 1:53 pm
OLAP Product: Cognos BI, TM1
Version: 9.5.2 - 10.1.1
Excel Version: Excel 2003

Re: Extract each character from an element

Post by ViRa »

Thanks Mackenzie for your time and assistance. In my case, the scramble logic is not based upon ASCII value. The code is user generated. That is, they have predefined replacements for few of the alphabets only and have provided us the list. The While loop needs to dynamically replace characters (that are available in the code list) from the Group ID.
rmackenzie
MVP
Posts: 733
Joined: Wed May 14, 2008 11:06 pm

Re: Extract each character from an element

Post by rmackenzie »

Ah, I misread it. So, create a dimension called 'Alphabet' with elements a-z. Create an attribute called 'Scramble Letter' and enter the appropriate user-defined substitute letter against the element. Then, the TI would be:

Code: Select all

vGroupID = CellGetS('Cube_Name',e1, e2, e3...,'GRP_ID');
vLenOfGroupID = LONG(vGroupID);
vScrambledGroupId = '';
x = 1;
While(x <= vLenOfGroupID);
  vLetter = SUBST ( vGroupID, x, 1 );
  vScambleLetter = ATTRS ( 'Alphabet', vLetter, 'Scramble Letter' );
  vScrambledGroupId = vScrambledGroupId | vScambleLetter;
  x = x + 1;
End;
CellPutS(vScrambledGroupId ,'Cube_Name',e1, e2, e3...,'GRP_ID');
Robin Mackenzie
ViRa
Regular Participant
Posts: 155
Joined: Tue May 14, 2013 1:53 pm
OLAP Product: Cognos BI, TM1
Version: 9.5.2 - 10.1.1
Excel Version: Excel 2003

Re: Extract each character from an element

Post by ViRa »

Thanks a lot Mackenzie for your time and assistance. I was able to run the process successfully and replace each of the characters accordingly. Thanks for showing me alternate ways of achieving the results.
Post Reply