4 Replies Latest reply: Oct 16, 2017 4:15 AM by Michel Bakker RSS

    Copy header to rowlevel

    Michel Bakker

      Who can find a great solution for me. I have some invoicedata which consists out of header en row information obove each other. Below an example of it.


      TypeSequenceValueDescription
      20010Invoice 7673528
      202223Apple
      202345Chair
      20040Invoice F767828B
      202534Belt
      202656Thee
      20070Inv XXF56
      202867Table


      The header is recognizes whit type 200. The goal is to transpose the header to the underlying row (type 202) til we have the next header (200). Below an example. You can see there is no reference between header and row. I think the only way is do it by a loop.


      TypeSequenceValueDescriptionInvouce header
      20010Invoice 7673528Invoice 7673528
      202223AppleInvoice 7673528
      202345ChairInvoice 7673528
      etcetcect


      Who can help me?



        • Re: Copy header to rowlevel
          Anil Babu

          Try to provide proper result set because etc also won't help.

          • Re: Copy header to rowlevel
            Michel Bakker

            I found a solution by my self. See below the script:

             

            T1:

            LOAD Factuur,

                 Type,

                 seqnr,

                 aantal,

                 tarief,

                 bedrag,

                 omschr ;

             

             

            LOAD * INLINE [

                Factuur, Type, seqnr, aantal, tarief, bedrag, omschr

                A, 200, 1, 0, 0, 0, Bon1

                A, 201, 2, 1, 2, 12, Appel

                A, 200, 3, 0, 0, 0, Bon2

                A, 201, 4, 3, 56, 65, Peer

                A, 201, 5, 44, 56, 54, stoel

                A, 201, 6, 56, 6, 54, Banaal

                A, 200, 7, 0, 0, 0, Bon3

                A, 201, 8, 54, 67, 232, Groente

                A, 200, 9, 0, 0, 0, Bon4

                A, 201, 10, 32, 32, 54, Tafel

                A, 201, 11, 232, 434, 343, Gebak

            ];

             

            T2:  /// sorting sequence

            NoConcatenate

            Load Factuur,

                 Type,

                 seqnr,

                 aantal,

                 tarief,

                 bedrag,

                 omschr

            Resident T1

            order by seqnr ;

             

            drop table T1 ;

             

            T3: // make 'omschr' empty

            Load

            Factuur,

                 Type,

                 seqnr,

                 aantal,

                 tarief,

                 bedrag,

                 omschr,

            If(Type = 200, omschr, null()) as Header 

            Resident T2 ;

             

            drop table T2 ;

             

            T4: // PEEK the new field header by his own value.

            NoConcatenate

            Load

                 Factuur,

                 Type,

                 seqnr,

                 aantal,

                 tarief,

                 bedrag,

                 omschr,

                

            If( IsNull( Header ), Peek( Header ), Header ) as Header

            Resident T3 ;

             

            drop table T3 ;

            • Re: Copy header to rowlevel
              Andrew Walker

              Hi Michel,

               

              Data:

              Load

              *,

              if(wildmatch(Description,'Inv*'),Description,Peek(Invoice)) as Invoice;

              LOAD * INLINE [

                  Type, Sequence, Value, Description

                  200, 1, 0, Invoice 7673528

                  202, 2, 23, Apple

                  202, 3, 45, Chair

                  200, 4, 0, Invoice F767828B

                  202, 5, 34, Belt

                  202, 6, 56, Thee

                  200, 7, 0, Inv XXF56

                  202, 8, 67, Table

              ];

               

              gives:

               

              Type Sequence Value Description Invoice
              20010Invoice 7673528Invoice 7673528
              202223AppleInvoice 7673528
              202345ChairInvoice 7673528
              20040Invoice F767828BInvoice F767828B
              202534BeltInvoice F767828B
              202656TheeInvoice F767828B
              20070Inv XXF56Inv XXF56
              202867TableInv XXF56

               

              Cheers

               

              Andrew

              • Re: Copy header to rowlevel
                Michel Bakker

                Thank you Andrew. The wildcard is nice but there are also invoices whit no text like 'inv'. So i simplify it a little bit.


                T1:

                LOAD * INLINE [

                    Factuur, Type, seqnr, aantal, tarief, bedrag, omschr

                    A, 200, 1, 0, 0, 0, Bon1

                    A, 201, 2, 1, 2, 12, Appel

                    A, 200, 3, 0, 0, 0, Bon2

                    A, 201, 4, 3, 56, 65, Peer

                    A, 201, 5, 44, 56, 54, stoel

                    A, 201, 6, 56, 6, 54, Banaal

                    A, 200, 7, 0, 0, 0, Bon3

                    A, 201, 8, 54, 67, 232, Groente

                    A, 200, 9, 0, 0, 0, Bon4

                    A, 201, 10, 32, 32, 54, Tafel

                    A, 201, 11, 232, 434, 343, Gebak

                ];

                 

                 

                T2: // maak de omschr leeg van de rijregels

                load *,

                If( IsNull( omschrNew ), Peek( Header ), omschr ) as Header ;

                 

                 

                Load

                Factuur,

                     Type,

                     seqnr,

                     aantal,

                     tarief,

                     bedrag,

                     omschr,

                If(Type = 200, omschr, null()) as omschrNew 

                Resident T1

                order by seqnr ;

                 

                 

                drop table T1 ;