Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.

Problem Transforming Data

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
1HAHeader
2aDetail1
3bDetail2
4cDetail3
5dDetail4
6HBHeader
7eDetail5
8HCHeader
9fDetail6
10gDetail7
11hDetail8
12iDetail9


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
1HAHA
2HAa1
3HAb2
4HAc3
5HAd4
6HBHB
7HBe5
8HCHC
9HCf6
10HCg7
11HCh8
12HCi9

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

];

1 Solution

Accepted Solutions

Re: Problem Transforming Data

Hi,

one solution could be:

QlikCommunity_Thread_143238_Pic1.JPG.jpg

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

3 Replies

Re: Problem Transforming Data

Hi,

one solution could be:

QlikCommunity_Thread_143238_Pic1.JPG.jpg

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

Re: Problem Transforming Data

Marco

Many thanks, that works perfectly.

Bill

Re: Problem Transforming Data

You're welcome

regards

Marco

Community Browser