Creating a subset with a while loop

Post Reply
samiamsz
Posts: 9
Joined: Wed Sep 02, 2020 3:08 pm
OLAP Product: tm1
Version: 11.3. 0.
Excel Version: 16

Creating a subset with a while loop

Post by samiamsz » Mon Sep 14, 2020 12:57 pm

Hey guys, I have created filtered subsets using the code below (i am new to tm1 so it may be somehwat sloppy)
I was wondering if there is a way i could do this using a while loop?

Code: Select all

IF(vCatDir @= 'Filter by Category' % Scan ('*',vCatDIR) =1);
	ITEMSKIP;
ENDIF;
sSub ='zzzCD-CAT'| vCatDIR;
#=====Create Subset For CATDIR ----> Cat====
smdx = '{FILTER({DESCENDANTS([Products].[' | vCatDir | '])},[Products].[Lvl] = "Cat")}';
IF ( SubsetExists ( 'Products' , sSub) = 1);
    SubsetDestroy ('Products', sSub );
EndIF;
SUBSETCREATEBYMDX(sSub, smdx);

smdxProd = '{FILTER({DESCENDANTS([Products].[' | vCatDir | '])},[Products].[Lvl] = "ProdUse")}';
sSubset ='zzzCD-PU'| vCatDIR;
IF ( SubsetExists ( 'Products' , sSubset) = 1);
    SubsetDestroy ('Products', sSubset );
EndIF;
SUBSETCREATEBYMDX(sSubset, smdxProd);

tomok
MVP
Posts: 2751
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Creating a subset with a while loop

Post by tomok » Mon Sep 14, 2020 1:39 pm

samiamsz wrote:
Mon Sep 14, 2020 12:57 pm
Hey guys, I have created filtered subsets using the code below (i am new to tm1 so it may be somehwat sloppy)
I was wondering if there is a way i could do this using a while loop?

IF(vCatDir @= 'Filter by Category' % Scan ('*',vCatDIR) =1);
ITEMSKIP;
ENDIF;
sSub ='zzzCD-CAT'| vCatDIR;
#=====Create Subset For CATDIR ----> Cat====
smdx = '{FILTER({DESCENDANTS([Products].[' | vCatDir | '])},[Products].[Lvl] = "Cat")}';
IF ( SubsetExists ( 'Products' , sSub) = 1);
SubsetDestroy ('Products', sSub );
EndIF;
SUBSETCREATEBYMDX(sSub, smdx);

smdxProd = '{FILTER({DESCENDANTS([Products].[' | vCatDir | '])},[Products].[Lvl] = "ProdUse")}';
sSubset ='zzzCD-PU'| vCatDIR;
IF ( SubsetExists ( 'Products' , sSubset) = 1);
SubsetDestroy ('Products', sSubset );
EndIF;
SUBSETCREATEBYMDX(sSubset, smdxProd);
Sure. You would just set up a while loop that either cycles through the entire dimension or another subset and then have SUBSETELEMENTINSERT's. MDX is by far the best way so I don't know why would want to do it in another way.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

Wim Gielis
MVP
Posts: 2527
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Creating a subset with a while loop

Post by Wim Gielis » Mon Sep 14, 2020 3:44 pm

Indeed, like Tom said, there are very few reasons why you would want to use a look here. One reason could be as an exercise for people new to TM1.

Your MDX code in a bit more condensed way would be:

Code: Select all

IF( vCatDir @= 'Filter by Category' % Scan( '*', vCatDIR ) = 1 );
   Itemskip;
ENDIF;
sSub = 'zzzCD-CAT'| vCatDIR;

#=====Create Subset For CATDIR ----> Cat====
smdx = 'FILTER( DESCENDANTS([Products].[' | vCatDir | ']), [Products].[Lvl] = "Cat" )';
SubsetDestroy( 'Products', sSub );
SubsetCreateByMDX( sSub, smdx );

smdxProd = 'FILTER( DESCENDANTS([Products].[' | vCatDir | ']), [Products].[Lvl] = "ProdUse" )';
sSubset = 'zzzCD-PU'| vCatDIR;
SubsetDestroy( 'Products', sSubset );
SubsetCreateByMDX( sSubset, smdxProd );
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

Post Reply