SKIP If doesn't exist instead of ERROR

Post Reply
AlphaWay
Posts: 6
Joined: Wed May 20, 2020 6:23 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: Version 2003

SKIP If doesn't exist instead of ERROR

Post by AlphaWay » Wed Jun 24, 2020 6:56 am

Hi All,

Base Table - ABC
Source Table - XYZ
Cube - Product
Dimension - PROD

Table : ABC
PROD | Measure
______________
Car | 2000
Bike | 1000
Cycle | 500
-----------------

Table : XYZ
PROD | Measure
______________
Car | 2000
Bike | 1000
Car | 3000
Car | 2000
-----------------


I have a TI Process to load "Product cube" from XYZ table.

I have a dimension "PROD" so Products(Car,Bike,Cycle) from ABC table would be the elements of PROD Dimension .
But the TI process throw errors when cube was loaded - Since i don't have product(Cycle) in "PROD" dim, they ended up in error.
I have to load data only for products that exist in "PROD" Dimension.
I need to Just skip others if it doesn’t exist instead of throwing error.
TIA,
Wayfarer

declanr
MVP
Posts: 1663
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: SKIP If doesn't exist instead of ERROR

Post by declanr » Wed Jun 24, 2020 7:23 am

Code: Select all

If ( Dimix ( <DimName>, <ProductName> ) = 0 );
	ItemSkip;
EndIf;
Declan Rodger

lotsaram
MVP
Posts: 3409
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: SKIP If doesn't exist instead of ERROR

Post by lotsaram » Wed Jun 24, 2020 7:32 am

This question is so basic I think it's appropriate to guide you to the request for assistance guidelines.

This is really TurboIntegrator scripting 101.
Usually you would make sure that metadata (that is dimension updates) is loaded in a separate process from a separate source, and that this happens BEFORE loading data. This avoids double-processing of data extracts and any potential locking. You can also avoid the errors during the data load without having metadata up to date in advance by either skipping records of elements that don't exist or inserting the elements before the CellPut.

E.g.

Code: Select all

### skipping
If( DimIx( cDimProduct, vProduct ) = 0 );
    ItemSkip;
EndIf;

### making sure element exists
If( DimIx( cDimProduct, vProduct ) = 0 );
    DimensionElementInsertDirect( cDimProduct, '', vProduct, 'N' );
EndIf;
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

AlphaWay
Posts: 6
Joined: Wed May 20, 2020 6:23 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: Version 2003

Re: SKIP If doesn't exist instead of ERROR

Post by AlphaWay » Wed Jun 24, 2020 9:57 am

Thanks @lotsaram and @declanr !

It worked :D
TIA,
Wayfarer

Post Reply