Adding another if loop on status

Post Reply
simon_pr3
Posts: 7
Joined: Fri Sep 22, 2023 7:36 am
OLAP Product: tm1 Planning Analytics
Version: tm1 Planning Analytics 2.0
Excel Version: 97

Adding another if loop on status

Post by simon_pr3 »

Hi guys,

I have following process, and he should only process the data if invstatus is one: So far I did not figure out where to put endif:

Here is untouched complete statement:

Code: Select all

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


AGruppe=CellGetS('ph_invsd',ph_versi,ph_ges,ph_gesna,ph_invplaner,ph_kst,ph_projektnr,ph_invobj,'InvArt1');
InvAnlass=CellGetS('ph_invsd',ph_versi,ph_ges,ph_gesna,ph_invplaner,ph_kst,ph_projektnr,ph_invobj,'InvAnlass');
InvNd=CellGetN('ph_invsd',ph_versi,ph_ges,ph_gesna,ph_invplaner,ph_kst,ph_projektnr,ph_invobj,'InvNd');
AJahr=CellGetS('ph_invsd',ph_versi,ph_ges,ph_gesna,ph_invplaner,ph_kst,ph_projektnr,ph_invobj,'InvJahr');
APeriode2=CellGetS('ph_invsd',ph_versi,ph_ges,ph_gesna,ph_invplaner,ph_kst,ph_projektnr,ph_invobj,'InvPerio');
Aperiode=if(StringToNumber(Aperiode2)<10,'P0','P')|APeriode2;
AHK=CellGetN('ph_invsd',ph_versi,ph_ges,ph_gesna,ph_invplaner,ph_kst,ph_projektnr,ph_invobj,'InvAHK');
Afa=CellGetN('ph_invsd',ph_versi,ph_ges,ph_gesna,ph_invplaner,ph_kst,ph_projektnr,ph_invobj,'InvAfa');

BewArtAHK=if(InvAnlass@='AA','ASHF02','ASHF01');
BewArtAfa=if(InvAnlass@='AA','ASFF02','ASFF01');
AHKWert=if(InvAnlass@='AA',-Afa*12*InvNd,AHK);
AfaAbg=AHK-AHKWert;

Jahr=ATTRS('ph_versi',Version,'StartJahr');
Version2=Version;
RestWert=AHK;

WriteData=1;
WriteAHK=1;
WriteAfa=1;
while(WriteData=1 & (WriteAHK=1 % WriteAfa=1));

  ZaePer=1;
  while(ZaePer<=12);

    Periode=if(ZaePer<10,'P0','P')|NumberToString(ZaePer);

    if(InvAnlass@<>'AA' & RestWert>0 & ((DIMIX('ph_jahr',AJahr)=DIMIX('ph_jahr',Jahr) & DIMIX('ph_perio',APeriode)<=DIMIX('ph_perio',Periode)) % DIMIX('ph_jahr',AJahr)<DIMIX('ph_jahr',Jahr)));
      AfaWert=if(Restwert-0.01>-Afa,Afa,-Restwert);
      RestWert=RestWert+AfaWert;
      AktWert=CellGetN('ph_inv1',Jahr,Periode,Version2,ph_ges,ph_gesna,ph_kst,'INVEB01','ASFF01',AGruppe);
      Wert=AktWert+Afa;
      CellPutN(Wert,'ph_inv1',Jahr,Periode,Version2,ph_ges,ph_gesna,ph_kst,'INVEB01','ASFF01',AGruppe);
    endif;

    if(InvAnlass@='AA' & RestWert<0 & ((DIMIX('ph_jahr',AJahr)=DIMIX('ph_jahr',Jahr) & DIMIX('ph_perio',APeriode)<DIMIX('ph_perio',Periode)) % DIMIX('ph_jahr',AJahr)<DIMIX('ph_jahr',Jahr)));
      AfaWert=if(Restwert+0.01<-Afa,Afa,-Restwert);
      RestWert=RestWert+AfaWert;
      AktWert=CellGetN('ph_inv1',Jahr,Periode,Version2,ph_ges,ph_gesna,ph_kst,'INVEB01','ASFF01',AGruppe);
      Wert=AktWert+Afa;
      CellPutN(Wert,'ph_inv1',Jahr,Periode,Version2,ph_ges,ph_gesna,ph_kst,'INVEB01','ASFF01',AGruppe);
    endif;
    if(RestWert=0);
      WriteAfa=0;
    endif;

    if(DIMIX('ph_jahr',AJahr)=DIMIX('ph_jahr',Jahr) & DIMIX('ph_perio',Periode)=DIMIX('ph_perio',APeriode));
      AktWert=CellGetN('ph_inv1',AJahr,APeriode,Version2,ph_ges,ph_gesna,ph_kst,'INVEB01',BewArtAHK,AGruppe);
      Wert=AktWert+AHKWert;
      CellPutN(Wert,'ph_inv1',AJahr,APeriode,Version2,ph_ges,ph_gesna,ph_kst,'INVEB01',BewArtAHK,AGruppe);
      if(InvAnlass@='AA');
        AktWert=CellGetN('ph_inv1',AJahr,APeriode,Version2,ph_ges,ph_gesna,ph_kst,'INVEB01',BewArtAfa,AGruppe);
        Wert=AktWert+AfaAbg;
        CellPutN(Wert,'ph_inv1',AJahr,APeriode,Version2,ph_ges,ph_gesna,ph_kst,'INVEB01',BewArtAfa,AGruppe);
      endif;
      WriteAHK=0;
    endif;
    
    ZaePer=ZaePer+1;

  end;
  
  Jahr=DIMNM('ph_jahr',DIMIX('ph_jahr',Jahr)+1);
  if(DIMIX('ph_jahr',Jahr)=0);
    FindVersion=0;
    WriteData=0;
  else;
    FindVersion=1;
  endif;

  while(FindVersion=1);
    if(DIMIX('ph_jahr',Jahr)<=DIMIX('ph_jahr',ATTRS('ph_versi',Version2,'EndJahr')));
      Version2=Version2;
      FindVersion=0;
    else;
      Version2=ATTRS('ph_versi',Version2,'AVersion');
    endif;
    if(DIMIX('ph_versi',Version2)=0);
      FindVersion=0;
      WriteData=0;
    endif;
  end;

end;
I figure I need to add the followign:
InvStatus=CellGetN('ph_invsd',ph_versi,ph_ges,ph_gesna,ph_invplaner,ph_kst,ph_projektnr,ph_invobj,'InvStatus');

IF(InvStatus=1);
// code goes here

but where to start, and where to put endif? So far I did not come up with a solution that worked. I started with putting it 'write...' but where would be the end?
ENDIF;

Best regards, Simon
Top
ascheevel
Community Contributor
Posts: 288
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: PA 2.0.9.1
Excel Version: 365
Location: Minneapolis, USA

Re: Adding another if loop on status

Post by ascheevel »

Formatting your code will go a long way in helping you to read/understand it and posting within a code block will go a long way in helping the rest of us help you. By the CellGetN you posted at the bottom for InvStatus, it appears you're using only the variables defined in the source view to derive InvStatus and it is not dependent on any additional variables defined within the data tab. If you simply want to skip over any record where invstatus=0, you could put an if and ItemSkip at the very top like I've done below to your reformatted code.

Code: Select all

## skip all records where InvStatus=0
IF(CellGetN('ph_invsd',ph_versi,ph_ges,ph_gesna,ph_invplaner,ph_kst,ph_projektnr,ph_invobj,'InvStatus') = 0);
    ItemSkip;
ENDIF;




AGruppe=CellGetS('ph_invsd',ph_versi,ph_ges,ph_gesna,ph_invplaner,ph_kst,ph_projektnr,ph_invobj,'InvArt1');
InvAnlass=CellGetS('ph_invsd',ph_versi,ph_ges,ph_gesna,ph_invplaner,ph_kst,ph_projektnr,ph_invobj,'InvAnlass');
InvNd=CellGetN('ph_invsd',ph_versi,ph_ges,ph_gesna,ph_invplaner,ph_kst,ph_projektnr,ph_invobj,'InvNd');
AJahr=CellGetS('ph_invsd',ph_versi,ph_ges,ph_gesna,ph_invplaner,ph_kst,ph_projektnr,ph_invobj,'InvJahr');
APeriode2=CellGetS('ph_invsd',ph_versi,ph_ges,ph_gesna,ph_invplaner,ph_kst,ph_projektnr,ph_invobj,'InvPerio');
Aperiode=if(StringToNumber(Aperiode2)<10,'P0','P')|APeriode2;
AHK=CellGetN('ph_invsd',ph_versi,ph_ges,ph_gesna,ph_invplaner,ph_kst,ph_projektnr,ph_invobj,'InvAHK');
Afa=CellGetN('ph_invsd',ph_versi,ph_ges,ph_gesna,ph_invplaner,ph_kst,ph_projektnr,ph_invobj,'InvAfa');

BewArtAHK=if(InvAnlass@='AA','ASHF02','ASHF01');
BewArtAfa=if(InvAnlass@='AA','ASFF02','ASFF01');
AHKWert=if(InvAnlass@='AA',-Afa*12*InvNd,AHK);
AfaAbg=AHK-AHKWert;

Jahr=ATTRS('ph_versi',Version,'StartJahr');
Version2=Version;
RestWert=AHK;

WriteData=1;
WriteAHK=1;
WriteAfa=1;
while(WriteData=1 & (WriteAHK=1 % WriteAfa=1));

    ZaePer=1;
    while(ZaePer<=12);

        Periode=if(ZaePer<10,'P0','P')|NumberToString(ZaePer);

        if(InvAnlass@<>'AA' & RestWert>0 & ((DIMIX('ph_jahr',AJahr)=DIMIX('ph_jahr',Jahr) & DIMIX('ph_perio',APeriode)<=DIMIX('ph_perio',Periode)) % DIMIX('ph_jahr',AJahr)<DIMIX('ph_jahr',Jahr)));
            AfaWert=if(Restwert-0.01>-Afa,Afa,-Restwert);
            RestWert=RestWert+AfaWert;
            AktWert=CellGetN('ph_inv1',Jahr,Periode,Version2,ph_ges,ph_gesna,ph_kst,'INVEB01','ASFF01',AGruppe);
            Wert=AktWert+Afa;
            CellPutN(Wert,'ph_inv1',Jahr,Periode,Version2,ph_ges,ph_gesna,ph_kst,'INVEB01','ASFF01',AGruppe);
        endif;

        if(InvAnlass@='AA' & RestWert<0 & ((DIMIX('ph_jahr',AJahr)=DIMIX('ph_jahr',Jahr) & DIMIX('ph_perio',APeriode)<DIMIX('ph_perio',Periode)) % DIMIX('ph_jahr',AJahr)<DIMIX('ph_jahr',Jahr)));
            AfaWert=if(Restwert+0.01<-Afa,Afa,-Restwert);
            RestWert=RestWert+AfaWert;
            AktWert=CellGetN('ph_inv1',Jahr,Periode,Version2,ph_ges,ph_gesna,ph_kst,'INVEB01','ASFF01',AGruppe);
            Wert=AktWert+Afa;
            CellPutN(Wert,'ph_inv1',Jahr,Periode,Version2,ph_ges,ph_gesna,ph_kst,'INVEB01','ASFF01',AGruppe);
        endif;
        if(RestWert=0);
            WriteAfa=0;
        endif;

        if(DIMIX('ph_jahr',AJahr)=DIMIX('ph_jahr',Jahr) & DIMIX('ph_perio',Periode)=DIMIX('ph_perio',APeriode));
            AktWert=CellGetN('ph_inv1',AJahr,APeriode,Version2,ph_ges,ph_gesna,ph_kst,'INVEB01',BewArtAHK,AGruppe);
            Wert=AktWert+AHKWert;
            CellPutN(Wert,'ph_inv1',AJahr,APeriode,Version2,ph_ges,ph_gesna,ph_kst,'INVEB01',BewArtAHK,AGruppe);
            if(InvAnlass@='AA');
                AktWert=CellGetN('ph_inv1',AJahr,APeriode,Version2,ph_ges,ph_gesna,ph_kst,'INVEB01',BewArtAfa,AGruppe);
                Wert=AktWert+AfaAbg;
                CellPutN(Wert,'ph_inv1',AJahr,APeriode,Version2,ph_ges,ph_gesna,ph_kst,'INVEB01',BewArtAfa,AGruppe);
            endif;
            WriteAHK=0;
        endif;

        ZaePer=ZaePer+1;

    end;

    Jahr=DIMNM('ph_jahr',DIMIX('ph_jahr',Jahr)+1);
    if(DIMIX('ph_jahr',Jahr)=0);
        FindVersion=0;
        WriteData=0;
    else;
        FindVersion=1;
    endif;

    while(FindVersion=1);
        if(DIMIX('ph_jahr',Jahr)<=DIMIX('ph_jahr',ATTRS('ph_versi',Version2,'EndJahr')));
            Version2=Version2;
            FindVersion=0;
        else;
            Version2=ATTRS('ph_versi',Version2,'AVersion');
        endif;
        if(DIMIX('ph_versi',Version2)=0);
            FindVersion=0;
            WriteData=0;
        endif;
    end;

end;
Top
ardi
Community Contributor
Posts: 152
Joined: Tue Apr 02, 2013 1:41 pm
OLAP Product: tm1, cognos bi
Version: from TM1 9.4 to PA 2.0.9.6
Excel Version: 2010
Location: Toronto, ON

Re: Adding another if loop on status

Post by ardi »

ascheevel wrote: ↑Mon Nov 06, 2023 2:09 pm Formatting your code will go a long way in helping you to read/understand it and posting within a code block will go a long way in helping the rest of us help you. By the CellGetN you posted at the bottom for InvStatus, it appears you're using only the variables defined in the source view to derive InvStatus and it is not dependent on any additional variables defined within the data tab. If you simply want to skip over any record where invstatus=0, you could put an if and ItemSkip at the very top like I've done below to your reformatted code.

Code: Select all

## skip all records where InvStatus=0
IF(CellGetN('ph_invsd',ph_versi,ph_ges,ph_gesna,ph_invplaner,ph_kst,ph_projektnr,ph_invobj,'InvStatus') = 0);
    ItemSkip;
ENDIF;




AGruppe=CellGetS('ph_invsd',ph_versi,ph_ges,ph_gesna,ph_invplaner,ph_kst,ph_projektnr,ph_invobj,'InvArt1');
InvAnlass=CellGetS('ph_invsd',ph_versi,ph_ges,ph_gesna,ph_invplaner,ph_kst,ph_projektnr,ph_invobj,'InvAnlass');
InvNd=CellGetN('ph_invsd',ph_versi,ph_ges,ph_gesna,ph_invplaner,ph_kst,ph_projektnr,ph_invobj,'InvNd');
AJahr=CellGetS('ph_invsd',ph_versi,ph_ges,ph_gesna,ph_invplaner,ph_kst,ph_projektnr,ph_invobj,'InvJahr');
APeriode2=CellGetS('ph_invsd',ph_versi,ph_ges,ph_gesna,ph_invplaner,ph_kst,ph_projektnr,ph_invobj,'InvPerio');
Aperiode=if(StringToNumber(Aperiode2)<10,'P0','P')|APeriode2;
AHK=CellGetN('ph_invsd',ph_versi,ph_ges,ph_gesna,ph_invplaner,ph_kst,ph_projektnr,ph_invobj,'InvAHK');
Afa=CellGetN('ph_invsd',ph_versi,ph_ges,ph_gesna,ph_invplaner,ph_kst,ph_projektnr,ph_invobj,'InvAfa');

BewArtAHK=if(InvAnlass@='AA','ASHF02','ASHF01');
BewArtAfa=if(InvAnlass@='AA','ASFF02','ASFF01');
AHKWert=if(InvAnlass@='AA',-Afa*12*InvNd,AHK);
AfaAbg=AHK-AHKWert;

Jahr=ATTRS('ph_versi',Version,'StartJahr');
Version2=Version;
RestWert=AHK;

WriteData=1;
WriteAHK=1;
WriteAfa=1;
while(WriteData=1 & (WriteAHK=1 % WriteAfa=1));

    ZaePer=1;
    while(ZaePer<=12);

        Periode=if(ZaePer<10,'P0','P')|NumberToString(ZaePer);

        if(InvAnlass@<>'AA' & RestWert>0 & ((DIMIX('ph_jahr',AJahr)=DIMIX('ph_jahr',Jahr) & DIMIX('ph_perio',APeriode)<=DIMIX('ph_perio',Periode)) % DIMIX('ph_jahr',AJahr)<DIMIX('ph_jahr',Jahr)));
            AfaWert=if(Restwert-0.01>-Afa,Afa,-Restwert);
            RestWert=RestWert+AfaWert;
            AktWert=CellGetN('ph_inv1',Jahr,Periode,Version2,ph_ges,ph_gesna,ph_kst,'INVEB01','ASFF01',AGruppe);
            Wert=AktWert+Afa;
            CellPutN(Wert,'ph_inv1',Jahr,Periode,Version2,ph_ges,ph_gesna,ph_kst,'INVEB01','ASFF01',AGruppe);
        endif;

        if(InvAnlass@='AA' & RestWert<0 & ((DIMIX('ph_jahr',AJahr)=DIMIX('ph_jahr',Jahr) & DIMIX('ph_perio',APeriode)<DIMIX('ph_perio',Periode)) % DIMIX('ph_jahr',AJahr)<DIMIX('ph_jahr',Jahr)));
            AfaWert=if(Restwert+0.01<-Afa,Afa,-Restwert);
            RestWert=RestWert+AfaWert;
            AktWert=CellGetN('ph_inv1',Jahr,Periode,Version2,ph_ges,ph_gesna,ph_kst,'INVEB01','ASFF01',AGruppe);
            Wert=AktWert+Afa;
            CellPutN(Wert,'ph_inv1',Jahr,Periode,Version2,ph_ges,ph_gesna,ph_kst,'INVEB01','ASFF01',AGruppe);
        endif;
        if(RestWert=0);
            WriteAfa=0;
        endif;

        if(DIMIX('ph_jahr',AJahr)=DIMIX('ph_jahr',Jahr) & DIMIX('ph_perio',Periode)=DIMIX('ph_perio',APeriode));
            AktWert=CellGetN('ph_inv1',AJahr,APeriode,Version2,ph_ges,ph_gesna,ph_kst,'INVEB01',BewArtAHK,AGruppe);
            Wert=AktWert+AHKWert;
            CellPutN(Wert,'ph_inv1',AJahr,APeriode,Version2,ph_ges,ph_gesna,ph_kst,'INVEB01',BewArtAHK,AGruppe);
            if(InvAnlass@='AA');
                AktWert=CellGetN('ph_inv1',AJahr,APeriode,Version2,ph_ges,ph_gesna,ph_kst,'INVEB01',BewArtAfa,AGruppe);
                Wert=AktWert+AfaAbg;
                CellPutN(Wert,'ph_inv1',AJahr,APeriode,Version2,ph_ges,ph_gesna,ph_kst,'INVEB01',BewArtAfa,AGruppe);
            endif;
            WriteAHK=0;
        endif;

        ZaePer=ZaePer+1;

    end;

    Jahr=DIMNM('ph_jahr',DIMIX('ph_jahr',Jahr)+1);
    if(DIMIX('ph_jahr',Jahr)=0);
        FindVersion=0;
        WriteData=0;
    else;
        FindVersion=1;
    endif;

    while(FindVersion=1);
        if(DIMIX('ph_jahr',Jahr)<=DIMIX('ph_jahr',ATTRS('ph_versi',Version2,'EndJahr')));
            Version2=Version2;
            FindVersion=0;
        else;
            Version2=ATTRS('ph_versi',Version2,'AVersion');
        endif;
        if(DIMIX('ph_versi',Version2)=0);
            FindVersion=0;
            WriteData=0;
        endif;
    end;

end;
I completely agree, when I read this kind of code, without any indentation and without any standard in naming variables and other objects, I get lost!
Ardian Alikaj
Top
simon_pr3
Posts: 7
Joined: Fri Sep 22, 2023 7:36 am
OLAP Product: tm1 Planning Analytics
Version: tm1 Planning Analytics 2.0
Excel Version: 97

Re: Adding another if loop on status

Post by simon_pr3 »

Thank it is working. BR Simon
Top
Post Reply

Return to “IBM TM1, Planning Analytics, PAx and PAW”