Looping in a Daily Dim

Post Reply
HighKeys
Posts: 97
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.9
Excel Version: Plus 2013

Looping in a Daily Dim

Post by HighKeys » Thu Feb 13, 2020 8:28 am

Hello,

i try to create a TI Loop that goes through all Days in a specific year (Day is the Element Level 0), in the loop i will do some calulations and write back the result in a cell at this day.

I think i have to create a subset and loop through the subset, would be nice if someone has a snipped from something like that (create subset an loop through it), i cant find any information in Google, or i'm just bad at google search.

Thanks for your help!

User avatar
orlando
Community Contributor
Posts: 136
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365

Re: Looping in a Daily Dim

Post by orlando » Thu Feb 13, 2020 8:41 am

Hi,

a short idea what do to. You have finalise by yourself.

Take a look here

https://www.bihints.com/book/export/html/68
this gives you all N elements of a parent
{TM1FILTERBYLEVEL({DESCENDANTS(Product.[Customer Lending]) }, 0)}

https://www.ibm.com/support/knowledgece ... tsize.html
SubsetGetSize - lets u know the number of elements in a subset

https://www.ibm.com/support/knowledgece ... tname.html
SubsetGetElementName - gives the element by index

the loop

i=0;
numElem = SubsetGet.....
while(i<=numElem);
sElem = SubsetGetElementName(DIm, Subset, i)

Do whatever you want

i=i+1;
end;

Best regards
orlando

HighKeys
Posts: 97
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.9
Excel Version: Plus 2013

Re: Looping in a Daily Dim

Post by HighKeys » Thu Feb 13, 2020 8:58 am

Awesome! Thank you so much!

I will post my code after i'm done!

HighKeys
Posts: 97
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.9
Excel Version: Plus 2013

Re: Looping in a Daily Dim

Post by HighKeys » Thu Feb 13, 2020 12:29 pm

Now i got a additional question, i set up my loops and calculation but if i trace the loop via ASCIIOUTPUT i see that its just looping the inner While and not the others..


i Build the Loops like:

Code: Select all

While(x <= y);

	do something
	
	while(c <= d);
		do something else
	end;

end;

Just the while(c <= d); loops correct. Is it not the correct syntax?

Thanks and KR

User avatar
orlando
Community Contributor
Posts: 136
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365

Re: Looping in a Daily Dim

Post by orlando » Thu Feb 13, 2020 12:34 pm

HighKeys wrote:
Thu Feb 13, 2020 12:29 pm

Just the while(c <= d); loops correct. Is it not the correct syntax?

Thanks and KR
think so, could you post the original code?

HighKeys
Posts: 97
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.9
Excel Version: Plus 2013

Re: Looping in a Daily Dim

Post by HighKeys » Thu Feb 13, 2020 12:37 pm

Hey Orlando, got my mistake, forgot to set the counter in the inner loops back to 1 thats why the loop just run once.

Here is my prolog script:

Code: Select all


#****Begin: Generated Statements***
#****End: Generated Statements****



#pFilter = 'Jahre: '|pJahr|' & Version: '|pQuellversion;

#build subsetname
sSubsetName = 'tmpCappingCalc_TI_' | today(1);


nProdCap = pProdCap \ 100;

#create tmp Subsets

if(SubsetExists('Tag', sSubsetName) = 1);
  SubsetDestroy('Tag', sSubsetName);
EndIf;
if(SubsetExists('Kontaktart', sSubsetName) = 1);
  SubsetDestroy('Kontaktart', sSubsetName);
EndIf;
if(SubsetExists('MA-Pool', sSubsetName) = 1);
  SubsetDestroy('MA-Pool', sSubsetName);
EndIf;

SubsetCreatebyMDX(sSubsetName, '{TM1FILTERBYLEVEL({DESCENDANTS(Tag.['|NumberToString(pJahr)|']) }, 0)}');
SubsetCreatebyMDX(sSubsetName, '{TM1FILTERBYLEVEL({DESCENDANTS(Kontaktart.[Kontaktarten Gesamt]) }, 0)}');
SubsetCreatebyMDX(sSubsetName, '{TM1FILTERBYLEVEL({DESCENDANTS([MA-Pool].[MA-Pool Gesamt]) }, 0)}');


#count elements in subsets
numEl = SubsetGetSize('Tag', sSubsetName);
kontaktArtsize = Subsetgetsize('Kontaktart', sSubsetName);
maPoolSize = Subsetgetsize('MA-Pool', sSubsetName);
peSize = Subsetgetsize('Planungseinheit', 'Alle n Level');

dayi = 1;
kontaktArtIndex = 1;
maPoolIndex = 1;
peIndex = 1;

#Loop all elements in Subset
while(dayi<=numEl);
  #get element name from index
  sDay = SubSetGetElementName('Tag', sSubsetName,dayi);
  
  while(kontaktArtIndex <= kontaktArtsize);
    sKontaktArt = SubsetGetElementName('Kontaktart', sSubsetName, kontaktArtIndex);
    while(maPoolIndex <= maPoolSize);
      sMaPool = SubsetGetElementName('MA-Pool', sSubsetName, maPoolIndex);
        while(peIndex <= peSize);
          sPE = SubsetGetElementName('Planungseinheit', 'Alle n Level', peIndex);
          
          nErlangMenge = CellGetN('Blending_Simulation', sDay, pVersion, sMApool, sPE, sKontaktArt, 'ErlangC Menge Lmin');
          nSonstigeMenge = CellGetN('Blending_Simulation', sDay, pVersion, sMApool, sPE, sKontaktArt, 'sonstige Menge Lmin');
          nBlendingMenge = CellGetN('Blending_Simulation', sDay, pVersion, sMApool, sPE, sKontaktArt, 'blending Menge Lmin');
          nMengeGesamt = nErlangMenge + nSonstigeMenge + nBlendingMenge;
          
          nErlangRMA = CellGetN('Blending_Simulation', sDay, pVersion, sMApool, 'Leistungsspektrum', sKontaktArt, 'Erlang rMA');
          nNichtGeblendeteRMA = CellGetN('Blending_Simulation', sDay, pVersion, sMApool, 'Leistungsspektrum', sKontaktArt, 'nicht geblendete rMA');
          nNochNichtGeblendeteRMA = CellGetN('Blending_Simulation', sDay, pVersion, sMApool, 'Leistungsspektrum', sKontaktArt, 'noch nicht geblendete rMA');
          nRmaProdCap = CellGetN('Blending_Simulation', sDay, pVersion, sMApool, 'Leistungsspektrum', sKontaktArt, 'noch nicht geblendete rMA');
          nGesamtRMA = nErlangRMA + nNichtGeblendeteRMA + nNochNichtGeblendeteRMA;
          
          prod = nMengeGesamt \ (nGesamtRMA * CellGetN('Blending_Simulation', sDay, pVersion, sMApool, sPE, sKontaktArt, 'Arbeitszeit in Min'));
          ASCIIOUTPUT('C:\Testo.txt',NumberToString(dayi), NumberToString(peIndex), sPE,'Day: ', sDay, sKontaktart, sMaPool, sPE, NumberToString(prod), NumberToString(nProdCap), 'ErlangMenge', NumberToString(nErlangMenge), 'nSonstigeMenge', NumberToString(nSonstigeMenge),'nBlendingMenge', NumberToString(nBlendingMenge) );
          While(prod > nProdCap);
            ASCIIOUTPUT('C:\Testo.txt', NumberToString(prod), NumberToString(nProdCap));
            nErlangMenge = CellGetN('Blending_Simulation', sDay, pVersion, sMApool, sPE, sKontaktArt, 'ErlangC Menge Lmin');
            nSonstigeMenge = CellGetN('Blending_Simulation', sDay, pVersion, sMApool, sPE, sKontaktArt, 'sonstige Menge Lmin');
            nBlendingMenge = CellGetN('Blending_Simulation', sDay, pVersion, sMApool, sPE, sKontaktArt, 'blending Menge Lmin');
            nMengeGesamt = nErlangMenge + nSonstigeMenge + nBlendingMenge;
            
            nErlangRMA = CellGetN('Blending_Simulation', sDay, pVersion, sMApool, sPE, sKontaktArt, 'Erlang rMA');
            nNichtGeblendeteRMA = CellGetN('Blending_Simulation', sDay, pVersion, sMApool, sPE, sKontaktArt, 'nicht geblendete rMA');
            nNochNichtGeblendeteRMA = CellGetN('Blending_Simulation', sDay, pVersion, sMApool, sPE, sKontaktArt, 'noch nicht geblendete rMA');
            nRmaProdCap = CellGetN('Blending_Simulation', sDay, pVersion, sMApool, sPE, sKontaktArt, 'noch nicht geblendete rMA');
            nGesamtRMA = nErlangRMA + nNichtGeblendeteRMA + nNochNichtGeblendeteRMA;
          
            
            CellPutN(CellGetN('Blending_Simulation', sDay, pVersion, sMApool, sPE, sKontaktArt, 'rmA Prod Capping')+0.5,'Blending_Simulation', sDay, pVersion, sMApool, sPE, sKontaktArt, 'rmA Prod Capping');
          end;
          peIndex = peIndex +1;
        end;
        peIndex =1;
      maPoolIndex = maPoolIndex +1;
    end;
    maPoolIndex = 1;
    kontaktArtIndex = kontaktArtIndex +1;
  end;
  kontaktArtIndex = 1;
  dayi=dayi+1;
end;

SubsetDestroy('Tag', sSubsetName);
SubsetDestroy('Kontaktart', sSubsetName);
SubsetDestroy('MA-Pool', sSubsetName);

Very bad code, i think its totally vs best practice but it works. :)

Thank you so much for your help!

Wim Gielis
MVP
Posts: 2529
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: Looping in a Daily Dim

Post by Wim Gielis » Thu Feb 13, 2020 12:38 pm

Please read the request for help guidelines:
https://www.tm1forum.com/viewtopic.php?f=3&t=1037

Many thanks.
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

Wim Gielis
MVP
Posts: 2529
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: Looping in a Daily Dim

Post by Wim Gielis » Thu Feb 13, 2020 12:41 pm

Normally, you would set the counter in the loop to the initial value (1 here) before the WHILE statement.

Consider also using a cube view as the data source of the process, instead of looping 3 times. This can be very inefficient (not necessarily so but oftentimes it is).
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

User avatar
orlando
Community Contributor
Posts: 136
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365

Re: Looping in a Daily Dim

Post by orlando » Thu Feb 13, 2020 1:07 pm

HighKeys wrote:
Thu Feb 13, 2020 12:37 pm
forgot to set the counter in the inner loops back to 1 thats why the loop just run once.
Not the last time you will forget this.....


Very bad code, i think its totally vs best practice but it works. :)
may be, but the code works - that is the most important thing.
now you can start with the optimization

Best regards
orlando

Post Reply