8 Replies Latest reply: Jan 18, 2016 2:14 PM by Roger Grossi RSS

    Problem with if expression

    Roger Grossi

      Hello,

      my expression:

      If((Min(MONTHYEAR) > Min(MONTHYEAR2)) and len(trim(FIELD1)<>0), Only(FIELD1B),FIELD1) as Value

       

      What I doing wrong?

        • Re: Problem with if expression
          Caleb Ochs

          Give this a shot

           

          If((Min(MONTHYEAR) > Min(MONTHYEAR2)) and len(trim(FIELD1))>0, Only(FIELD1B),FIELD1) as Value



          • Re: Problem with if expression
            Kiruthigadevi Subramaniam

            Hi,

            Try the expressions separately without the AND condition and check it out. If everything works fine then move ahead with AND condition. Could you post your sample app?

              • Re: Problem with if expression
                Roger Grossi

                Code:

                Table:

                LOAD *,

                  If(Len(Trim(MonthName(MakeDate(YEAR, MONTH)))) = 0, MonthName(MakeDate(YEAR2, MONTH2)), MonthName(MakeDate(YEAR, MONTH))) as MONTHYEAR,

                  MonthName(MakeDate(YEAR2, MONTH2)) as MONTHYEAR2

                Inline [

                ID,MONTH,YEAR,FIELD1,FIELD2,MONTH2,YEAR2,FIELD1B

                2,2,2005,10,B,3,2005,40

                2,5,2005,40,D,,,40

                23,11,2008,25,C,,,30

                23,2,2009,30,H,,,30

                100,,,,,6,2010,555

                500,3,2015,80,X,1,2015,58

                500,6,2015,60,W,,,58

                333,4,2014,100,AA,8,2014,100

                333,6,2014,300,BB,,,100

                ];

                 

                Join (Table)

                LOAD ID,

                  Value,

                  MonthName(AddMonths(Min, IterNo()-1)) as MONTHYEAR

                While AddMonths(Min, IterNo()) <= Max;

                LOAD ID,

                  If(Max(MONTHYEAR) < Max(MONTHYEAR2), Alt(Max(MONTHYEAR2), Max(MONTHYEAR)), Max(MONTHYEAR)) as Max,

                  If(Min(MONTHYEAR) < Min(MONTHYEAR2), Min(MONTHYEAR), Alt(Min(MONTHYEAR2), Min(MONTHYEAR))) as Min,



                //I Want to change this expression

                  If(Max(MONTHYEAR) < Max(MONTHYEAR2) or Min(MONTHYEAR) > Min(MONTHYEAR2), Only(FIELD1B)) as Value

                //HERE I NEED:

                /*

                IF(Min(MONTHYEAR) > Min(MONTHYEAR2) and isnull(FIELD1),FIELD1B,if(Min(MONTHYEAR) > Min(MONTHYEAR2) and len(FIELD1)>0,FIELD1)) as Value

                 

                 

                I need this:

                1.-if(Min(MONTHYEAR) > Min(MONTHYEAR2) and isnull(FIELD1) output FIELD1B

                2.-if(Min(MONTHYEAR) > Min(MONTHYEAR2) and isnotnull(FIELD1) output FIELD1

                */




                Resident Table

                Group By ID;

                 

                FinalTable:

                NoConcatenate

                LOAD ID,

                  MONTH,

                  YEAR,

                  MONTHYEAR,

                  Alt(Alt(FIELD1, FIELD1B), Value) as FIELD1,

                  FIELD2;

                LOAD ID,

                  Num(Month(MONTHYEAR)) as MONTH,

                  Year(MONTHYEAR) as YEAR,

                  MONTHYEAR,

                // MONTH2,

                // YEAR2,

                  FIELD1B,

                  Value,

                  If(Len(Trim(FIELD1)) = 0, IF(ID = Previous(ID), Peek('FIELD1')), FIELD1) as FIELD1,

                  If(Len(Trim(FIELD2)) = 0, IF(ID = Previous(ID), Peek('FIELD2')), FIELD2) as FIELD2

                Resident Table

                Order By ID, MONTHYEAR;

                 

                DROP Table Table;

                //////////////////////


                  • Re: Problem with if expression
                    Stefan Wühl

                    You need aggregation functions for all fields that you don't list in your GROUP BY clause:

                     

                    IF(Min(MONTHYEAR) > Min(MONTHYEAR2) and isnull(Only(FIELD1)),Only(FIELD1B),if(Min(MONTHYEAR) > Min(MONTHYEAR2) and len(Only(FIELD1))>0,Only(FIELD1))) as Value


                    or maybe just


                    IF(Min(MONTHYEAR) > Min(MONTHYEAR2) and isnull(Only(FIELD1)),Only(FIELD1B),Only(FIELD1) ) as Value


                    Not sure if this is what you want, though. Probably Sunny can help you with the logic.


                      • Re: Problem with if expression
                        Roger Grossi

                        With this inline table:

                        Inline [

                        ID,MONTH,YEAR,FIELD1,FIELD2,MONTH2,YEAR2,FIELD1B

                        1000,3,2015,rev,bb,10,2014,soul

                        1000,5,2015,soul,rev,10,2014,soul

                        555,3,2015,50,40,2,2015,80

                        555,4,2015,80,50,2,2015,80

                        100,,,,,6,2010,555];

                         

                        IF MONTH2<MONTH AND FIELD2 IS NOT NULL THEN FINAL_FIELD1=FIELD2

                        IF MONTH2<MONTH AND FIELD2 IS NULL THEN FINAL_FIELD1=FIELD1B

                         

                        I only use FIELD1B if month2 and year2 are <month and year and FIELD2 is null

                        but i always use month2 and year2 for the table

                         

                        Output:

                        ID,MONTH,YEAR,FINAL_FIELD1,FINAL_FIELD2

                        1000,10,2014,bb,-

                        1000,11.2014,bb,-

                        1000,12,2014,bb,-

                        1000,1,2015,bb,-

                        1000,2,205,bb,-

                        1000.3,2015,rev,bb

                        1000,4,2015,rev,bb

                        1000,5,2015,soul,rev

                        555,2,2015,40,-

                        555,3,2015,50,40

                        555,4,2015,80,50

                        100,6,2010,555

                         

                         

                        Thank you for your answers

                        • Re: Problem with if expression
                          Roger Grossi

                          My code now:

                          Table:

                          LOAD *,

                            If(Len(Trim(MonthName(MakeDate(YEAR, MONTH)))) = 0, MonthName(MakeDate(YEAR2, MONTH2)), MonthName(MakeDate(YEAR, MONTH))) as MONTHYEAR,

                            MonthName(MakeDate(YEAR2, MONTH2)) as MONTHYEAR2

                          Inline [

                          ID,MONTH,YEAR,FIELD1,FIELD2,MONTH2,YEAR2,FIELD1B

                          1000,3,2015,rev,bb,10,2014,soul

                          1000,5,2015,soul,rev,10,2014,soul

                          555,3,2015,50,40,2,2015,80

                          555,4,2015,80,50,2,2015,80

                          100,,,,,6,2010,555];

                           

                          Join (Table)

                          LOAD ID,

                            Value,

                            MonthName(AddMonths(Min, IterNo()-1)) as MONTHYEAR

                          While AddMonths(Min, IterNo()) <= Max;

                          LOAD ID,

                            MonthName(addmonths(today(),-0)) as Max,

                            If(Min(MONTHYEAR) < Min(MONTHYEAR2), Min(MONTHYEAR), Alt(Min(MONTHYEAR2), Min(MONTHYEAR))) as Min,

                           

                           

                          IF(Min(MONTHYEAR) > Min(MONTHYEAR2) and isnull(Only(FIELD1)),Only(FIELD1B),if(Min(MONTHYEAR) > Min(MONTHYEAR2) and len(Only(FIELD1))>0,Only(FIELD1))) as Value

                           

                           

                          Resident Table

                          Group By ID;

                           

                          FinalTable:

                          NoConcatenate

                          LOAD ID,

                          Value,

                            MONTH,

                            YEAR,

                            MONTHYEAR,

                            If( Len(Trim(FIELD1)), FIELD1, If( Len(Trim(FIELD1B)), FIELD1B, Value)) as FIELD1,

                            FIELD2;

                          LOAD ID,

                            Num(Month(MONTHYEAR)) as MONTH,

                            Year(MONTHYEAR) as YEAR,

                            MONTHYEAR,

                            FIELD1B,

                            Value,

                            If(Len(Trim(FIELD1)) = 0, IF(ID = Previous(ID), Peek('FIELD1')), FIELD1) as FIELD1,

                            If(Len(Trim(FIELD2)) = 0, IF(ID = Previous(ID), Peek('FIELD2')), FIELD2) as FIELD2

                          Resident Table

                          Order By ID, MONTHYEAR;

                           

                          DROP Table Table;

                           

                           

                          But the output is:

                          ID MONTH YEAR FIELD1 FIELD2 MONTHYEAR Value
                          5552201580 Feb 201580
                          555320155040Mar 201580
                          555420158050Apr 201580
                          555520158050May 201580
                          555620158050Jun 201580
                          555720158050Jul 201580
                          555820158050Aug 201580
                          555920158050Sep 201580
                          5551020158050Oct 201580
                          5551120158050Nov 201580
                          5551220158050Dec 201580

                           

                          And I need:

                          Output:

                          ID,MONTH,YEAR,FINAL_FIELD1,FINAL_FIELD2

                          1000,10,2014,bb,-

                          1000,11.2014,bb,-

                          1000,12,2014,bb,-

                          1000,1,2015,bb,-

                          1000,2,205,bb,-

                          1000.3,2015,rev,bb

                          1000,4,2015,rev,bb

                          1000,5,2015,soul,rev

                          555,2,2015,40,-

                          555,3,2015,50,40

                          555,4,2015,80,50

                          100,6,2010,555

                           

                          I think the problem happens when I have MONTH2 and YEAR2 < MONTH and YEAR, and I have a FIELD1 and FIELD2. I need put FIELD2(or FIELD1, it´s no problem) in the previous date.

                           

                          Thank you.

                        • Re: Problem with if expression
                          Srikanth P

                          Try with nested IF's  like below:

                           

                          IF( Min(MONTHYEAR) > Min(MONTHYEAR2) ,

                               IF( Len(Trim(FIELD1)) , FIELD1 , FIELD1B

                                  )

                             )