3 Replies Latest reply: Oct 19, 2017 11:06 AM by Adam Hughes RSS

    Help using Peek/Previous

    Adam Hughes

      Hello,

       

      I have data like:

       

      RecordNoExtension No.Extension
      12426-
      2-A Smith
      3-A Smith
      152427-
      16-M Adams
      17-M Adams
      18-M Adams
      292427-
      30-M Adams
      31-M Adams
      32-M Adams
      462428-
      47-C Kelly
      502429-
      51-L Sypniewska
      52-L Sypniewska

       

      I need to populate "Extension No." with the number from the previous row.

       

      E.G:

       

      RecordNoExtension No.Extension
      12426-
      22426A Smith
      3

      2426

      A Smith

       

      Have tried some peek/previous logic but with no success. Any help is appreciated.

        • Re: Help using Peek/Previous
          Sunny Talwar

          May be this

           

          LOAD RecordNo,

               If(Len(Trim([Extension No.])) = 0, Peek('NewExtNo'), [Extension No.]) as NewExtNo,

               If(Len(Trim([Extension])) = 0, Peek('NewExt'), Extension) as NewExt

          Resident Fact

          Order By RecordNo;

           

          DROP Table Fact;

           

          RENAME Field NewExtNo to [Extension No.];

          RENAME Field NewExt to Extension;

          • Re: Help using Peek/Previous
            Isabelle Timmermans

            TEST:

            Load * Inline [

                RecordNo,ExtensionNR,Extension

                1,2426,-

                2,-,A Smith

                3,-,A Smith

                15,2427,-

                16,-,M Adams

                17,-,M Adams

                18,-,M Adams

                29,2427,-

                30,-,M Adams

                31,-,M Adams

                32,-,M Adams

                46,2428,-

                47,-,C Kelly

                50,2429,-

                51,-,L Sypniewska

                52,-,L Sypniewska

                ];

             

            mapTeST:

            Mapping LOAD

              Extension,

                 IF(WildMatch(ExtensionNR, '-'), Previous(ExtensionNR), ExtensionNR)

            Resident TEST

            Where IF(WildMatch(ExtensionNR, '-'), Previous(ExtensionNR), ExtensionNR) <>'-';   

             

             

            NoConcatenate

            TEST:

            LOAD RecordNo,

              IF(WildMatch(ExtensionNR, '-'), Applymap('mapTeST', Extension), ExtensionNR) as ExtensionNR,

              Extension

            Resident TEST;

             

             

            Drop table TEST;