Problem Transforming Data

[Anonymous]

Hi

I have this format of input data. Which has a Header rows and Detail rows.  It comes in this order and also has a Row number to sort the data if needed.

Row	Description	Type	Value
1	HA	Header
2	a	Detail	1
3	b	Detail	2
4	c	Detail	3
5	d	Detail	4
6	HB	Header
7	e	Detail	5
8	HC	Header
9	f	Detail	6
10	g	Detail	7
11	h	Detail	8
12	i	Detail	9

And I would like to transform it into this format, such that the the Header Description is repeated until a new Header.

Row	Header	Detail	Value
1	HA	HA
2	HA	a	1
3	HA	b	2
4	HA	c	3
5	HA	d	4
6	HB	HB
7	HB	e	5
8	HC	HC
9	HC	f	6
10	HC	g	7
11	HC	h	8
12	HC	i	9

I thought it would be easy but have completely failed.

Any suggestions ?

****************************************************************

Here is an inline load to create the input data

InputData :

LOAD * INLINE [

    Row, Description, Type, Value

    1, HA, Header

    2, a, Detail, 1

    3, b, Detail, 2

    4, c, Detail, 3

    5, d, Detail, 4

    6, HB, Header

    7, e, Detail, 5

    8, HC, Header

    9, f, Detail, 6

    10, g, Detail, 7

    11, h, Detail, 8

    12, i, Detail, 9

];

[MarcoWedel · Accepted solution]

Hi,

one solution could be:

LOAD Row,
    If(Type='Header', Description, Peek(Header)) as Header,
    Description as Detail,
    Value
FROM [http://community.qlik.com/thread/143238] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco

[Anonymous]

Marco

Many thanks, that works perfectly.

Bill

[MarcoWedel]

You're welcome

regards

Marco