Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

michelbakker
New Contributor II

Copy header to rowlevel

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?



1 Solution

Accepted Solutions
michelbakker
New Contributor II

Re: Copy header to rowlevel

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 ;

4 Replies

Re: Copy header to rowlevel

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

michelbakker
New Contributor II

Re: Copy header to rowlevel

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
Honored Contributor

Re: Copy header to rowlevel

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

michelbakker
New Contributor II

Re: Copy header to rowlevel

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 ;

Community Browser