Adding Unique elements to a subset

[CiskoWalt]

Hello,

I currently populate a subset with the elements (dates) that exist on the source file. The subset is used in a view and the view is used to identify cube records where the measure must be zeroed out before loading the new volume.

Date Product Volume
1/1/2011 A 888
1/1/2011 A 44
1/1/2011 B 21
1/2/2011 B 3
1/2/2011 C 10

In the MetaData Tab I delete all of the elements in the Subset

DimPeriod = 'VodDay';
subRef = 'zzTiVODPriceDates';
sOrderDay = TRIM(v_order_day);

#^^^ Delete All of The Elements From The zzTiVODPriceDates Subset In the VodDay Dimension
SubsetDeleteAllElements(DimPeriod, subRef);


In the Data Tab I populate the element in the SubSet

#^^^ Create the subset dynamically in VodDates Dimension

DimPeriod = 'VodDay';
subRef = 'zzTiVODPriceDates';
sOrderDay = TRIM(v_order_day);


#^^^ Update the zzTiVODPriceDates Subset in the VodDay Dimension with the date values from the File.
SUBSETELEMENTINSERT (dimPeriod, subRef, sOrderDay, 1);


This works; however, there are duplicate values in teh subset. Using the sample data provided above, there will be 5 records in the subset (3 for 1/1/2011 and 2 for 1/2/2011). The file I loaded recently has 150,000 records for the January activity.


How can I only add unique elements to the subset?

Thanks,

Walt

[Marcus Scherer]

Hi Walt,
if your dates are ordered you could do the following:

in Prolog add:

# memorizes previous date, initialization only here
sOrderDayPrev = '';

in Metadata tab no change.
in Data tab add:

IF(sOrderDay @<> sOrderDayPrev);
SUBSETELEMENTINSERT (dimPeriod, subRef, sOrderDay, 1);
sOrderDayPrev = sOrderDay;
ENDIF;


HTH,

Marcus

[CiskoWalt]

Thank You Marcus.

[lotsaram]

Easiest way to create a subset with unique elements is to insert into a temporary dimension on the metadata then on the epilog loop through the temp dimension and build the subset.

[ETHSSN]

...does somebody also know a rule how to count the leaf elements on a (unique) subset or dimension like this? Moreover like a count(distinct <leafelements>) on the dimensions leafs?

Best regards,
Stefan

[declanr]

How do you want the output? Do you want to retrieve the number for use in a TI process? For use in a rule? Just for personal intrigue?

Easiest way to create a subset with unique elements is to insert into a temporary dimension on the metadata then on the epilog loop through the temp dimension and build the subset.

... same general principle could be used but with a DimSiz function at the end instead.

[tomok]

...does somebody also know a rule how to count the leaf elements on a (unique) subset or dimension like this? Moreover like a count(distinct <leafelements>) on the dimensions leafs?

Rules are written against cubes, not dimensions, so there is no COUNT function in rules. You could get a count of the number of elements in a hierarchy point, which could theoretically represent the whole dimension or a subset of the dimension, by placing a value of 1 in every leaf cell. You could also post the size of a dimension to a cell via a rule like:

['Size']=DIMSIZ('Dim');