Unable to delete elements

[Lakshmi]

Hi All,
I am trying to delete all the elements which starts with 'A' in a dimension 'Test'.
List of the elements:
------------------------
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec

Below is the code which I have written in Metadata tab.
Dim='Test';
i=0;
while (i<=dimsiz(dim));
element=dimnm(dim,i);
if (subst(element,1,1)@='A',dimensionelementdelete(dim,element),itemskip);
i=i+1;
end;

The process is completed successfully but the elements in the dimension aren't deleted.
Can you please tell where I am going wrong.

Thanks,
Lakshmi

[lotsaram]

This is on the metadata tab you say? In which case you have a data source? Which has how many records?

You need to understand what the purpose of the while loop is and what ItemSkip does in the context of processing of code on either the metadata or data tabs. Itemskip will escape out of the processing for the data source record entirely so once this is encountered the rest of the loop won't be stepped through. If Jan is element index 1 then it loop will exit, it will never get to Feb ...

You could just remove the itemskip clause alltogether and you should get the result you want.
BUT
I would argue that you shouldn't put the while loop on the metadata at all. EITHER you use the while loop on the prolog and iterate through the dimension with the loop OR you have no while loop at all and use a subset as the data source and only have the name check and deletion on the metadata.

Here's what I mean:

For code on prolog option do this:

Code: Select all

# loop through dimension
sDimName = 'Test';
nCounter = 1;
nMaximum = DimSiz(sDimName);
WHILE(nCounter <= nMaximum);
  sElName = DimNm(sDimName, nCounter);
  IF(SubSt(sElName, 1, 1) @= 'A' );
    DimensionElementDelete(sDimName, sElName);
    nMaximum = nMaximum - 1;
  Else;
    nCounter = nCounter + 1;
  EndIF;
END;

(note that by deleting an element you change the indices of all elements after it. So to avoid skipping over the next element when the deletion condition is met ONLY increment the counter when NOT deleting an element AND remember to reduce the maximum in order to avoid "never ending while loop" syndrome.)

The alternative is to use the data source as your implicit loop and have no while loop at all. I would go for this approach (below), but as others have said there is no one best way!
For code on metadata option do this:
Set data source as Subset "ALL"

Code: Select all

IF(SubSt(sElName, 1, 1) @= 'A' );
  DimensionElementDelete(sDimName, sElName);
EndIF;

OR

Code: Select all

IF(SubSt(sElName, 1, 1) @<> 'A' );
  ItemSkip;
Else;
  DimensionElementDelete(sDimName, sElName);
EndIF;

(I wouldn't do the 2nd option personally, just giving you an example of how to use ItemSkip)

[Lakshmi]

It worked.
I really appreciate the way you explained me. It is pretty much clear to me now.
Thank you very much.

[Suharsh]

Hello,

I have a question regarding the use of ItemSkip. I am using Itemskip in metadata tab.

The Criteria is such that in the metadata tab, this is the code

If (DIMIX('Customer', vCustomer)=0);

ItemSkip;

EndIf;

In the data tab, I am using a cellputN function.

But I am getting an error which says, the incoming value in vCustomer does not exist in the dimension.

So that means the ItemSkip is not working for me, the record still goes in the data tab.

When I use another code in Metadata,

If(DIMIX('Customer',vCustomer)=0);
flag = 1;
EndIf;

And Use the below code in Data tab, it works

If (flag = 1);
itemskip;
else;
CellPutN ( vData, sCubeName, vCustomer, vProduct, vMonth, vMeasure );
endif;

Can anyone please advise on this issue with ItemSkip?

Thanks in advance

[declanr]

Hello,

I have a question regarding the use of ItemSkip. I am using Itemskip in metadata tab.

The Criteria is such that in the metadata tab, this is the code

If (DIMIX('Customer', vCustomer)=0);

ItemSkip;

EndIf;

In the data tab, I am using a cellputN function.

But I am getting an error which says, the incoming value in vCustomer does not exist in the dimension.

So that means the ItemSkip is not working for me, the record still goes in the data tab.

When I use another code in Metadata,

If(DIMIX('Customer',vCustomer)=0);
flag = 1;
EndIf;

And Use the below code in Data tab, it works

If (flag = 1);
itemskip;
else;
CellPutN ( vData, sCubeName, vCustomer, vProduct, vMonth, vMeasure );
endif;

Can anyone please advise on this issue with ItemSkip?

Thanks in advance

Just stick your code in both the data and metadata tabs.

Code: Select all

If (DIMIX('Customer', vCustomer)=0); 

ItemSkip;

EndIf;

When I use another code in Metadata,

If(DIMIX('Customer',vCustomer)=0);
flag = 1;
EndIf;

And Use the below code in Data tab, it works

If (flag = 1);
itemskip;
else;
CellPutN ( vData, sCubeName, vCustomer, vProduct, vMonth, vMeasure );
endif;

Are you sure this works?

TI's run through the whole host of datasource rows for the metadata tab and THEN through the whole lot for the data tab.

So in your instance (unless you are also resetting Flag = 0 somewhere) if any single 1 of your items turns on the FLAG = 1; it will be on for all the following rows in the metadata tab and EVERY row of your data tab... as such it would skip your whole data tab.

[Suharsh]

Well, as per my understanding, every single row gets processed by metadata and then by data tab and then the second row is picked up, which follows the same path.

Have you read it somewhere on IBM site about execution of records like this? If so, please let me know

I will test my data as per what you said, and will post the results tomorrow morning. But it will be something good to know how it works.

Thanks

[mattgoff]

Well, as per my understanding, every single row gets processed by metadata and then by data tab and then the second row is picked up, which follows the same path.

No, the Metadata tab processes all rows of source data, then the Data tab processes all rows of source data. ItemSkip needs to be performed on each tab separately if required. There are situations where you would only want to skip a row on one tab but process on the other, hence the apparent redundancy.

Matt