Data extract where leaf value <>0, but consol = 0

[tez]

Hi

We have a TI process that currently extracts all natural accounts entries over each month for a year from a cube view. Currently the extract includes all values, no matter if they are zero or not.

I am trying to get the output file that includes a line for the natural account for each month & full year total if there is a value somewhere in at least one month, even if it is an in & out transaction over different months, so if say, $1,000 is shown in Jul, but -$1,000 is shown in Aug, with a full year total of $0, we still need the line to appear in the extract.

I am having trouble finding how to do this, within the one TI process. I have basic knowledge of TI Processes - this process was written by an ex employee, & I am trying to modify it to get it working properly. Originally, it was only extracting natural account lines with values in them, & I have included the ViewExtractSkipZeroesSet to now include zeroes. I just need it to go that extra step!

Greatly appreciate any help on this.

Regards & many thanks
Terri

[rksharmatm1]

If i understand correctly, you want to extract lines for months only if they have non zero values, and for total_Year - extract the line in both the cases, whether it has 0 or any value.

create the extract view as you are doing it now
just before exporting the data into file, put a IF condition (assumption that months elements, Total_Year element is in same dimension)

--> check if it is month (leaf level elements), then extract only those which have measure value <>0 against them
--> For total_year ( consolidation element), no condition, every line will be exported into file.

Hope it helps..

[declanr]

Hi

We have a TI process that currently extracts all natural accounts entries over each month for a year from a cube view. Currently the extract includes all values, no matter if they are zero or not.

I am trying to get the output file that includes a line for the natural account for each month & full year total if there is a value somewhere in at least one month, even if it is an in & out transaction over different months, so if say, $1,000 is shown in Jul, but -$1,000 is shown in Aug, with a full year total of $0, we still need the line to appear in the extract.

I am having trouble finding how to do this, within the one TI process. I have basic knowledge of TI Processes - this process was written by an ex employee, & I am trying to modify it to get it working properly. Originally, it was only extracting natural account lines with values in them, & I have included the ViewExtractSkipZeroesSet to now include zeroes. I just need it to go that extra step!

Greatly appreciate any help on this.

Regards & many thanks
Terri

Think it sounds like you are using an asciioutput() on the data tab of your process?

In order to show:
Months where the value <> 0
Full Year where the value of any child month <> 0

I would simply have ViewExtractSkipZeroesSet to 0 (i.e. show all rows regardless of whether there is a value or not)

Then add the following at the top of your data tab (and/or metadata if you are doing similar things in there)

Code: Select all

If ( DTYPE ( 'Month', vMonth ) @= 'N' );
     If ( Value <> 0 );
           nSkip = 0;
     Else;
           nSkip = 1;
     EndIf;
Else;
     nSkip = 1;
     iCount = 1;
     iMax = 12;
     While ( iCount <= iMax );
         sMonth = ElComp ( 'Month', vMonth, iCount );
         If ( CellGetN ( sCub, v1, sMonth, v3, v4, v5 )<>0);
              nSkip = 0;
              iCount = 12;
         EndIf;
         iCount = iCount + 1;
     End;
EndIf;

If ( nSkip = 1 );
     ItemSkip;
EndIf;

sCub is the cube in your data source.
vMonth is the variable of your month dimension
v1, v3, v4, v5 are the other variables (assuming its a 5 dim cube and month is dim 2)

HTH

[tomok]

I would simply have ViewExtractSkipZeroesSet to 0 (i.e. show all rows regardless of whether there is a value or not)

Depending on the sparsity in the cube this could be quite expensive, time-wise. Another option would be to add another element to the measure dimension, called ABS_Amount and calculate it as ABS(RegularMeasure). Build your view, zero-suppressed, on this measure. Then in the data tab have a CellGetN formula to get the original measure to output.

[tez]

Thanks for all your posts....I've tried some solutions & they don't seem to be working (could be because of my lack of knowledge though)!

Just to clarify, I need an asciioutput txt file, comma delimited, that gives me a line for each natural account number, along with Jul Amt, Aug Amt, Sep Amt......Jun Amt for the financial year if any of those months contain a value <> 0. If only one month has a value <> 0, I need the zeros from all the other months also showing in the extract. The only time a natural account number will not appear in the extract is when all months for that account = 0.

The heading of the txt file currently looks like:

"ACN","01/AMT1","02/AMT1","03/AMT1","04/AMT1","05/AMT1","06/AMT1","07/AMT1","08/AMT1","09/AMT1","10/AMT1","11/AMT1","12/AMT1","FullYear"

where ACN is natural account number, & then the values for each month. I had put the FullYear on the end to test that it was bringing through ACNs where the FullYear = 0.

I'm not sure if this changes some of your solutions you have given me, or has given you some extra info that helps.

Many thanks
Terri

[tez]

Ok, I worked it out! I have added in the code

Code: Select all

if(AMT01<>0 % AMT02<>0 % AMT03<>0 % AMT04<>0......)

after the extract of all values & before the asciioutput line, it will export all month values for only those natural account numbers that have a value in any AMTxx column <> 0.

Thanks for all your ideas & help.

Cheers
Terri