Copy header to rowlevel

[michelbakker]

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.

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

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.

Type	Sequence	Value	Description	Invouce header
200	1	0	Invoice 7673528	Invoice 7673528
202	2	23	Apple	Invoice 7673528
202	3	45	Chair	Invoice 7673528
etc	etc	ect

Who can help me?

[Anil_Babu_Samineni]

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

[michelbakker]

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 ;

[effinty2112]

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
200	1	0	Invoice 7673528	Invoice 7673528
202	2	23	Apple	Invoice 7673528
202	3	45	Chair	Invoice 7673528
200	4	0	Invoice F767828B	Invoice F767828B
202	5	34	Belt	Invoice F767828B
202	6	56	Thee	Invoice F767828B
200	7	0	Inv XXF56	Inv XXF56
202	8	67	Table	Inv XXF56

Cheers

Andrew

[michelbakker · Accepted solution]

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 ;