TM1 Forum

Hi

I have exported Cube View to Text file using TextOutput function.
But can anyone help in suggesting ways to export zero value from cube view to text file without unchecking Skip Zero/Blank Values or usint TI function ViewExtractSkipZeroesSet.

ThankYou in advance:)

sg2012 wrote:But can anyone help in suggesting ways to export zero value from cube view to text file without unchecking Skip Zero/Blank Values or usint TI function ViewExtractSkipZeroesSet.

Seems like an oxymoron. Either you want to skip blanks or you don't, and the method to not skip them is to set ViewExtractSkipZeroesSet=0

To back up what lotsaram said in TM1 it regards zeros and blanks as the same value, zero, unless you are talking about elements.

Is UNDEFVALS set in the cube rule. This will have a significant effect on the behaviour.

lotsaram wrote:Either you want to skip blanks or you don't, and the method to not skip them is to set ViewExtractSkipZeroesSet=0

There are circumstances where the OPs question is quite valid. Consider the scenario where you need to export the GL actuals for a year which is only part complete, and the file must have the months as column headers with separate rows for each account/ centre/ etc combination. In this case you would want to capture that some future months have zero balances but you still need a zero-suppressed view of the cube. The solution in this case is to keep skip zeroes but allow consolidations in the view. Then lock down the view such that only the full year consolidation is included meaning you can iterate over the months for each input row of the data source and output the zero balance values without having to take the performance hit of removing the zero suppression.

Obviously this is a stab in the dark, perhaps sg2012 can elaborate on the question?

rmackenzie wrote:The solution in this case is to keep skip zeroes but allow consolidations in the view. Then lock down the view such that only the full year consolidation is included

Hello Robin,

The issue is / could be that total year is zero but some month(s) outweight(s) other month(s) fully.

Say:

Jan: -100
Feb: +50
Mar: +50
Apr-Dec: 0

Nothing shows up in the file...

I have had this issue too in the past.
To solve it, during the export process I created a new temporary cube with similar dimensions, less the Months dimension.
I storing in the temporary cube a 1 on combinations that need to be exported for the full year. The data source view is all lowest level data you need to export.
Part 2 of the process: exporting 12 monthly values from the data cube, based on the 1's in the temporary cube.
Last bit of the process is clean up, don't forget the temporary cube (although it's low on memory footprint).

Wim Geilis wrote:The issue is / could be that total year is zero but some month(s) outweight(s) other month(s) fully.

Yes, absolutely... your method solves this and is definitely the better solution.

lotsaram wrote:

sg2012 wrote:But can anyone help in suggesting ways to export zero value from cube view to text file without unchecking Skip Zero/Blank Values or usint TI function ViewExtractSkipZeroesSet.

Seems like an oxymoron. Either you want to skip blanks or you don't, and the method to not skip them is to set ViewExtractSkipZeroesSet=0

I want a LOGIC to include zero or blank value in my text file.

sg2012 wrote:

lotsaram wrote:

sg2012 wrote:But can anyone help in suggesting ways to export zero value from cube view to text file without unchecking Skip Zero/Blank Values or usint TI function ViewExtractSkipZeroesSet.

Seems like an oxymoron. Either you want to skip blanks or you don't, and the method to not skip them is to set ViewExtractSkipZeroesSet=0

I want a LOGIC to include zero or blank value in my text file.

The LOGIC is to set ViewExtractSkipZeroesSet to either 0 or 1 depending on whether you want the zeroes or not.

As Robin indicated if this approach isn't working for you in some way, you need to elaborate on why it isn't and what it is, specifically, that you are trying to do.

rmackenzie wrote:There are circumstances where the OPs question is quite valid. Consider the scenario where you need to export the GL actuals for a year which is only part complete, and the file must have the months as column headers with separate rows for each account/ centre/ etc combination. In this case you would want to capture that some future months have zero balances but you still need a zero-suppressed view of the cube....
.... Obviously this is a stab in the dark, perhaps sg2012 can elaborate on the question?

I don't disagree, and I never said that exporting zero values wasn't valid. A more logical scenario might be wanting to export zero balances of actualized months but still skip future months, or export all balances including nulls for valid Business unit / Cost Centre / Account combinations but skip invalid combinations.

HOWEVER I was trying not to second guess the OP's question, just answer it. (An approach that I recall you recently endorsed ! )

Maybe UNDEFALS is what they are looking for (I wouldn't recommend it). But it really isn't clear, especially considering the OP's subsequent response. Without any adequate additional information I think the response from Alan is the appropriate one.