Parsing Elements name to ASCII file

[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:

Code: Select all

100.21.3100
100.325.45000

I want to split these based on the . (dots) so that the export through ASCIIOutput() will look like:

Code: Select all

100,21,3100
100,325,45000

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?

[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;

[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!

[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

[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!