Page 1 of 1
Parsing Elements name to ASCII file
Posted: Thu Sep 02, 2010 12:01 am
by ExApplix
I am trying to export data from a cube to ASCII file. My diemension elements have . (dots) in them which I am using to create roll-ups. For example:
I want to split these based on the . (dots) so that the export through ASCIIOutput() will look like:
I think I will have to use the While loop to Scan() the . (dot) as the dot apprears at variable length.
Can someone please help me in spliting these elements names?
Re: Parsing Elements name to ASCII file
Posted: Thu Sep 02, 2010 4:23 pm
by ajain86
Here is some code to get you started.
Assumptions made:
You want 1 dimension member split out per 1 line which forced me to not use another While loop.
The dimension member only has 3 parts to it.
Code: Select all
#===================================================================
# Variables
#===================================================================
sDim = 'temp';
nSize = DIMSIZ( sDim );
nCount = 1;
sFile = ''| sDim |'_export.txt';
#===================================================================
# Loop through dimension members
#===================================================================
While( nCount <= nSize );
#===================================================================
# First value
#===================================================================
sMem = DIMNM( sDim, nCount );
nLen = LONG( sMem );
nDot = SCAN( '.', sMem );
s1 = SUBST( sMem, 1, nDot - 1 );
#===================================================================
# Second value
#===================================================================
sMem = SUBST( sMem, nDot + 1, nLen );
nLen = LONG( sMem );
nDot = SCAN( '.', sMem );
s2 = SUBST( sMem, 1, nDot - 1 );
#===================================================================
# Third value
#===================================================================
sMem = SUBST( sMem, nDot + 1, nLen );
nLen = LONG( sMem );
s3 = SUBST( sMem, 1, nLen );
#===================================================================
# Output
#===================================================================
ASCIIOutput( sFile, s1, s2, s3 );
#===================================================================
# Increase counter
#===================================================================
nCount = nCount + 1;
END;
Re: Parsing Elements name to ASCII file
Posted: Thu Sep 02, 2010 10:20 pm
by ExApplix
Thanks for the reply but actually my requirements are slightly different. I am trying to export the data from the
Cube to a Text file. Sample data could be something like:
Code: Select all
PRODUCT, AMOUNT
100.101.20,-290201
100.121.50,3567
1020.101.700,-1500
1020.101,-1600
The product code has got three parts i.e. Product_Code, Sub_Product and Component. So if you look at the element 100.101.20 - 100 is the Product_Code, 101 is the Sub_Product and 20 is the Component. The length of Product_Code, Sub_Product and Component could vary - its not fixed number of characters. Also in the out of the 3 fields (Product_Code, Sub_Product and Component) one or two fields could be missing e.g. If there is no Component then the element name would be 1020.101
Can you anyone please help me in writing While loop so that I will get the following ASCII output:
Code: Select all
100,101,20,-290201
100,121,50,3567
1020,101,700,-1500
1020,101,-1600
Any help would be highly appreciated!
Re: Parsing Elements name to ASCII file
Posted: Thu Sep 02, 2010 10:40 pm
by Malcolm MacDonnell
Presuming that your variables are called Product and Amount:
DatasourceASCIIQuoteCharacter='';
CommaProduct = '';
nLen = Long (Product);
nIndex = Scan ('.' Product);
While (nIndex <> 0);
Product = SubSt (Product, 1, nIndex - 1) | ',' | SubSt (Product, nIndex + 1, nLen);
nIndex = Scan ('.' Product);
End;
AsciiOutput ('myfile.txt', Product, Amount);
This won't work if your amount has commas as there are no quotes. I just typed this straight in so there might be syntax or logic errors, but you should get the general drift.
Mal
Re: Parsing Elements name to ASCII file
Posted: Fri Sep 03, 2010 6:35 pm
by ExApplix
Thanks guys for your efforts.
Malcolm you are a rock star - your solution really helped me to nail it down. I had a fair idea of which string functions to use but was not sure which condition should I put in the While loop.
Thanks again!