topic Re: Copy header to rowlevel in QlikView

Copy header to rowlevel

michelbakker — Sat, 14 Oct 2017 15:38:34 GMT

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

Invoice 7673528

202

Apple

Invoice 7673528

202

Chair

Invoice 7673528

etc

ect

Who can help me?

Re: Copy header to rowlevel

Anil_Babu_Samineni — Mon, 16 Oct 2017 02:04:46 GMT

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

Re: Copy header to rowlevel

michelbakker — Mon, 16 Oct 2017 06:54:30 GMT

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

effinty2112 — Mon, 16 Oct 2017 07:52:32 GMT

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

Re: Copy header to rowlevel

michelbakker — Mon, 16 Oct 2017 08:15:10 GMT

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 ;