6 Replies Latest reply: Jul 8, 2013 7:10 AM by Vishwaranjan Kumar RSS

    Check if Previous Previous field is null

      Hello,

       

      I am trying to fill previous empty fields with the value of the previous row.

      I will explain this with an example. I am using the following table.

       

      KeyPartMonthVoorraad
      ExamplePart101-01-201321
      ExamplePart101-02-201314
      ExamplePart101-03-201312
      ExamplePart101-04-201377
      ExamplePart101-05-2013-
      ExamplePart101-06-201370
      ExamplePart101-07-201370
      ExamplePart201-01-201333
      ExamplePart201-02-201327
      ExamplePart201-03-201325
      ExamplePart201-04-2013-
      ExamplePart201-05-2013-
      ExamplePart201-06-201320
      ExamplePart201-07-201320

       

      I am using the following code in my script:

      if(IsNull([Stock]),Previous([Stock]), [Stock]) as [Stock],

       

      This works (partially), and I get the following result:

       

       

      KeyPartMonthVoorraad
      ExamplePart101-01-201321
      ExamplePart101-02-201314
      ExamplePart101-03-201312
      ExamplePart101-04-201377
      ExamplePart101-05-201370
      ExamplePart101-06-201370
      ExamplePart101-07-201370
      ExamplePart201-01-201333
      ExamplePart201-02-201327
      ExamplePart201-03-201325
      ExamplePart201-04-2013-
      ExamplePart201-05-201320
      ExamplePart201-06-201320
      ExamplePart201-07-201320

       

       

      Only problem is the 01-04-2013 date for ExamplePart2.

       

      I have alot of cases where 2 or more months are empty. How can I change the IF statement so it fills, lets say, even 6 months back?

       

      I was trying the following:

       

      if(IsNull([Stock]),Previous([Stock]), 
                if(isNull(Previous([Stock])), Previous(Previous([Stock])), [Stock])) as [Stock],
      

       

      But this doesnt seem to work. Any ideas?

        • Re: Check if Previous Previous field is null
          Tresesco B

          Try This:

           

          if(IsNull([Stock]),Peek(New_Stock), [Stock]) as New_Stock

          • Re: Check if Previous Previous field is null
            Vishwaranjan Kumar

            if([Stock]=' ',Previous([Stock]), [Stock]) as [Stock],

            • Re: Check if Previous Previous field is null
              Vishwaranjan Kumar

              try this

               

               

              temp:

              LOAD * INLINE [

                  KeyPart, Month, Voorraad

                  ExamplePart1, 01-01-2013, 21

                  ExamplePart1, 01-02-2013, 14

                  ExamplePart1, 01-03-2013, 12

                  ExamplePart1, 01-04-2013, 77

                  ExamplePart1, 01-05-2013, -

                  ExamplePart1, 01-06-2013, 70

                  ExamplePart1, 01-07-2013, 70

                  ExamplePart2, 01-01-2013, 33

                  ExamplePart2, 01-02-2013, 27

                  ExamplePart2, 01-03-2013, 25

                  ExamplePart2, 01-04-2013, -

                  ExamplePart2, 01-05-2013, -

                  ExamplePart2, 01-06-2013, 20

                  ExamplePart2, 01-07-2013, 20

              ];

               

              LOAD *,

              if(Voorraad='-',Previous(Voorraad),Voorraad) as Voorraad_new

              Resident temp;

               

              then output like this

               

              KeyPartMonthVoorraad_new
              ExamplePart101-01-201321
              ExamplePart101-02-201314
              ExamplePart101-03-201312
              ExamplePart101-04-201377
              ExamplePart101-05-201377
              ExamplePart101-06-201370
              ExamplePart101-07-201370
              ExamplePart201-01-201333
              ExamplePart201-02-201327
              ExamplePart201-03-201325
              ExamplePart201-04-201325
              ExamplePart201-05-2013-
              ExamplePart201-06-201320
              ExamplePart201-07-201320

               

              see attachement

              • Re: Check if Previous Previous field is null
                florentina dogaru

                try:

                if(Voorraad='',Peek(Voorraad_new),Voorraad) as Voorraad_new

                • Re: Check if Previous Previous field is null
                  Dave Riley

                  If your data is like this at source, then you may be able to use the Transformation step to fill in missing values, otherwise you may also be able to export the table to a qvd and read it back in with transformation step ...

                   

                  Store A into A.qvd (qvd);

                  Drop Table A;

                   

                  A:

                  LOAD @1 as KeyPart,

                       @2 as Month,

                       @3 as Stock

                  FROM

                  [A.qvd]

                  (qvd, filters(

                  Replace(3, top, StrCnd(equal, '-'))

                  ));

                   

                  flipside

                  • Re: Check if Previous Previous field is null
                    Vishwaranjan Kumar

                    in expression side--

                    try this-

                     

                    in straight--

                    dimension1-    KeyPart

                    dimension2-    Month

                    expression-   if(Voorraad='-',Below(Voorraad),Voorraad)

                     

                    then outpul like this

                    KeyPartMonthif(Voorraad='-',Below(Voorraad),Voorraad)
                    -
                    ExamplePart101-01-201321
                    ExamplePart101-02-201314
                    ExamplePart101-03-201312
                    ExamplePart101-04-201377
                    ExamplePart101-05-201370
                    ExamplePart101-06-201370
                    ExamplePart101-07-201370
                    ExamplePart201-01-201333
                    ExamplePart201-02-201327
                    ExamplePart201-03-201325
                    ExamplePart201-04-2013-
                    ExamplePart201-05-201320
                    ExamplePart201-06-201320
                    ExamplePart201-07-201320