TM1 Forum

Discussing all things TM1, Planning Analytics, PAx and PAW
https://www.tm1forum.com/

Adding another if loop on status

https://www.tm1forum.com/viewtopic.php?t=16573

Page 1 of 1

Adding another if loop on status

Posted: Mon Nov 06, 2023 1:27 pm
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

Re: Adding another if loop on status

Posted: Mon Nov 06, 2023 2:09 pm
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;

Re: Adding another if loop on status

Posted: Mon Nov 06, 2023 3:17 pm
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!

Re: Adding another if loop on status

Posted: Wed Nov 08, 2023 12:55 pm
by simon_pr3
Thank it is working. BR Simon
All times are UTC
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited