TM1 Forum

Posted: **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);

Posted: **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.

Posted: **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 );

TM1 Forum

Creating a subset with a while loop

Creating a subset with a while loop

Re: Creating a subset with a while loop

Re: Creating a subset with a while loop