Cube Data Export

[dfrench77]

Does anyone know if there is a TI function that will export a all cube data both string and numeric in the same manner as right clicking the cube and choosing 'export to text file'. I tried ASCIIOutput, TextOutput, and right clicking cube and chosing 'export to text file' however, I only get the string values and not the numerical values. Am I missing something in these functions?

[BigG]

try numbertostring function

put

Code: Select all

   IF( VALUE_IS_STRING <> 1 );
     ASCIIOUTPUT( filename, vDim1, vDim2, vDim3,vvalue);
   ELSE;
     ASCIIOUTPUT(  filename, vDim1, vDim2, vDim3,numbertostring( vvalue),);
   ENDIF;

[Alan Kirk]

Does anyone know if there is a TI function that will export a all cube data both string and numeric in the same manner as right clicking the cube and choosing 'export to text file'. I tried ASCIIOutput, TextOutput, and right clicking cube and chosing 'export to text file' however, I only get the string values and not the numerical values. Am I missing something in these functions?

It might be an idea to post your exact code. BigG is right about needing to convert a value to a string, but if you aren't doing that already you should actually be getting a syntax error ("invalid string expression") when you try to save the process since AsciiOutput can't work with a numeric argument.

[lotsaram]

If you are processing a view you shouldn't need to check for data type. You can just use the inbuilt sValue variable.

[Alan Kirk]

If you are processing a view you shouldn't need to check for data type. You can just use the inbuilt sValue variable.

If it's a mixture of strings and numbers, you do still to need to check for type unless you're OK with generating an error log. sValue is the conversion of numeric values to a string, and works fine when the value is numeric, but if you use it when the value is already a string you get an error "Cannot convert field number n, value "x" to a real number". Which of course isn't what you'd expect, since you don't want it to be a real number, you want a string. My guess is that it's trying to read in the cube value as a number so that it can convert it to a string, then getting a shock when it finds out the the cube value isn't one. (This is the behaviour that I noticed in past versions but I'm still seeing it in 9.5.1.)

Interestingly the help file is still bass-ackwards on this claiming that:

SValue
When the DatasourceType is 'VIEW', this TurboIntegrator local variable determines the value of the current cell when Value_Is_String is not 0. (That is, when the current cell contains a string.)

Except, as I just confirmed by test... if the value is a string, all you get from sValue is a minor error in an error log.

I'd usually do the test that BigG recommended just to prevent the error log from generating, though I usually use the Str() function to do the conversion rather than sValue or numbertostring only because it gives me finer control over the number of decimal places. To each their own on that one.

[lotsaram]

Weird that is not what you would expect. Usually I just shortcut the lookup and export sValue. I suppose when I am doing this it is always to export numeric data only but this is definately A over T.

So if data is mixed you need..

IF( value_is_string = 1 );
ASCIIOutput(filename, ...., Value);
Else;
ASCIIOutput(filename, ...., sValue);
EndIF;

What a load of ......

[Steve Rowe]

If you are only doing an ASCIIOutput then you can just set the value variable to a string in the variables tab and then you don't do anything special when you get to the actual export.
Or am I missing something?

[Alan Kirk]

If you are only doing an ASCIIOutput then you can just set the value variable to a string in the variables tab and then you don't do anything special when you get to the actual export.
Or am I missing something?

Apparently not.

(Personally I still wouldn't do it that way because it provides no decimal truncation and I'm not wildly crazy about how integer values export with a decimal point but nothing after it, but it's a perfectly valid option.)

[dfrench77]

All,
Thanks for you many and varied comments. In file attachment I provide sample cube view and sample of the Asciioutput file. Thanks

Steve - I tried setting the value variable to a string and not performing any conditonal test or converting to numeric to string, but I only get the string data output in the text file. See attached file

Lotsaram - Using the sValue code that you recommended save with out a syntax error but the export file only generated the string data. See attached file.

IF( value_is_string = 1 );
ASCIIOUTPUT('C:\TM1_Applications\TM1_Servers\TransferCube.cma', Departments, Employees, vSalary_Fields, Value);
Else;
ASCIIOUTPUT('C:\TM1_Applications\TM1_Servers\TransferCube.cma', Departments, Employees, vSalary_Fields,sValue);
EndIF;

Alan and BigG - The number to string code throws an invalid string expression error either when the value variable is set to string or numeric. See attached file
IF( VALUE_IS_STRING <> 1 );
ASCIIOUTPUT('C:\TM1_Applications\TM1_Servers\TransferCube.cma', Departments, Employees, vSalary_Fields, Value);
ELSE;
ASCIIOUTPUT('C:\TM1_Applications\TM1_Servers\TransferCube.cma', Departments, Employees, vSalary_Fields,numbertostring(Value),);
ENDIF;

[lotsaram]

If the export is generating only string data and you see numeric data also in the view
- is the numeric data rule calculated?
- are the rules properly fed?

Unless rule calculated leaf values are fed the records will be skipped if skipping zero values.

[dfrench77]

Lotsram - Yes the numerica data and string data were calculated by rules and the feeders were not in place. Thanks for your help and guidance.

Regards,

dfrench77