Looping in a Daily Dim
-
- Posts: 117
- Joined: Fri Aug 09, 2019 10:11 am
- OLAP Product: TM1 / TM1 Web / Perspectives
- Version: Planning Analytics V2.0.9
- Excel Version: Office 365
Looping in a Daily Dim
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!
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!
- orlando
- Community Contributor
- Posts: 167
- 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
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
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
-
- Posts: 117
- Joined: Fri Aug 09, 2019 10:11 am
- OLAP Product: TM1 / TM1 Web / Perspectives
- Version: Planning Analytics V2.0.9
- Excel Version: Office 365
Re: Looping in a Daily Dim
Awesome! Thank you so much!
I will post my code after i'm done!
I will post my code after i'm done!
-
- Posts: 117
- Joined: Fri Aug 09, 2019 10:11 am
- OLAP Product: TM1 / TM1 Web / Perspectives
- Version: Planning Analytics V2.0.9
- Excel Version: Office 365
Re: Looping in a Daily Dim
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:
Just the while(c <= d); loops correct. Is it not the correct syntax?
Thanks and KR
i Build the Loops like:
Code: Select all
While(x <= y);
do something
while(c <= d);
do something else
end;
end;
Thanks and KR
-
- Posts: 117
- Joined: Fri Aug 09, 2019 10:11 am
- OLAP Product: TM1 / TM1 Web / Perspectives
- Version: Planning Analytics V2.0.9
- Excel Version: Office 365
Re: Looping in a Daily Dim
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:
Very bad code, i think its totally vs best practice but it works.
Thank you so much for your help!
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!
-
- MVP
- Posts: 3118
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Looping in a Daily Dim
Please read the request for help guidelines:
https://www.tm1forum.com/viewtopic.php?f=3&t=1037
Many thanks.
https://www.tm1forum.com/viewtopic.php?f=3&t=1037
Many thanks.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- MVP
- Posts: 3118
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Looping in a Daily Dim
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).
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
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
- orlando
- Community Contributor
- Posts: 167
- 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
Not the last time you will forget this.....
may be, but the code works - that is the most important thing.
Very bad code, i think its totally vs best practice but it works.
now you can start with the optimization
Best regards
orlando