14 Replies Latest reply: Jan 20, 2015 11:29 AM by Joseph Simmons RSS

    How to fill empty lines

      Hello everyone,


      I have a table that looks like this :


      XXX         Status_before         Status_after               Timestamp

      A                lala                          lili                        01/01/2015 03:00:00

      A                   -                           -                           01/01/2015  04:00:00

      A                  -                            -                           01/01/2015  05:00:00

      A                lili                            lulu                       01/01/2015  05:15:34

      B                xaxa                       xixi                        01/01/2015   03:43:35




      I would like that the "-" (that represent null values)  to be filled with the previous value of status_after (the table is already sorted).

      In that case, I would like the empty lines to be filled with "lili".


      It has to be done only if the value of "XXX" is the same, that means if I have


      B                xaxa                       xixi                        01/01/2015   03:43:35

      C                  -                            -                            01/01/2015   02:00:00

      C                 -                            -                              01/01/2015  03:00:00

      C                lala                        lili                           01/01/2015    03:23:45



      The 2 lines with empty values have to stay like this.



      I hope I am clear enough.

      Thank you for your help.


      Have a good day



        • Re: How to fill empty lines
          Avinash R


          Try like this in the Expression:

          if(Status_before=null() or Status_before='' and XXX=previous(XXX),previous(Status_after))

          • Re: How to fill empty lines
            Joseph Simmons

            Hi Laura,


            I would look to do populate the values within the script, if your data is sorted correctly as you load it in, then you can combine an if isnull() check with the peek() function to evaluate previous rows when the current row is null and meets your criteria


            hope that helps


            • Re: How to fill empty lines
              ISHFAQUE AHMED

              Dear LauraS056,


              I would recommended you to do that by using "Edit Script > Table Files > Next > Enable Transformation Step >  Tab Fill > Click on Fill Button Left Side > and Put your condition".


              It's proper way which gives you long term solution as well as there are so many others features available which helps you in future just explore them.



              Kind regards,

              Ishfaque Ahmed

              • Re: How to fill empty lines
                neetha P

                Hi Laura,


                Try below code in script:



                load XXX,
                if(XXX= Previous(XXX) and len(Trim(Status_after)) = 0,Previous(Status_after),Status_after) as Status_after,
                biff, embedded labels, table is Sheet1$);




                • Re: How to fill empty lines

                  Hi everyone,


                  thank you for your answers, unfortunately, I didn't figure out to make it work.


                  I have tried :


                  if(isnull(Status_before) and isnull(Status_after) and previous(XXX)=XXX, peek(Status_after), Status_before) as Status_before,


                  but it doesn't work   I have also tried with previous instead of peek.

                  • Re: How to fill empty lines
                    Malek Safa

                    use the enable transformation step when loading the file, and go to Fill tab, here you can put your case by selecting the cell condition and is empty, and in the fill type you can select the behavior, Above to take the above field, below to take the below field from the empty cell.


                    • Re: How to fill empty lines
                      Marco Wedel



                      one solution could be:






                      LOAD XXX, 
                           If(Len(Status_before) or XXX<>Previous(XXX),Status_before,Peek(Status_after)) as Status_before,
                           If(Len(Status_after) or XXX<>Previous(XXX),Status_after,Peek(Status_after)) as Status_after,
                      INLINE [
                          XXX, Status_before, Status_after, Timestamp
                          A, lala, lili, 01/01/2015 03:00:00
                          A,,, 01/01/2015 04:00:00
                          A,,, 01/01/2015 05:00:00
                          A, lili, lulu, 01/01/2015 05:15:34
                          B, xaxa, xixi, 01/01/2015 03:43:35


                      hope this helps





                      • Re: How to fill empty lines

                        Hi everyone,


                        there must be something wrong.


                        I have tried (just to see if at least this would work) :


                        LOAD *,

                        peek(Status_before) as Status_before2

                        resident T2;


                        And I get :


                        XXX         Status_before         Status_after               Timestamp                          Status_before2

                        A                lala                          lili                        01/01/2015 03:00:00                        -

                        A                   -                           -                           01/01/2015  04:00:00                      lala

                        A                  -                            -                           01/01/2015  05:00:00                       -

                        A                lili                            lulu                       01/01/2015  05:15:34                      -

                        B                xaxa                       xixi                        01/01/2015   03:43:35                     lili



                        The peek function give me the previous value but if the previous value is null, I do get a null instead of getting the last not null value