How to convert one column to two rows whilst other columns remain the same.

[rachelpurple]

Hi,

My source table is like below:

Host	Cluster
1	a
2	a
3	b
4	b
5	c
6	c
7	d
8	d
9	e
10	e
11	f
12	f

but I want to make it flatter in qlikview, like below:

Host 1	Host 2	Cluster
1	2	a
3	4	b
5	6	c
7	8	d
9	10	e
11	12	f

How can I realise this in load script? or in straight table / pivot table?

[sunny_talwar · Accepted solution]

May be like this:

Table:

LOAD Host,

    Cluster,

    AutoNumber(Host, Cluster) as Key

FROM

[https://community.qlik.com/thread/220822]

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

LOAD Host as Host1,

  Cluster

Resident Table

Where Key = 1;

Join (FinalTable)

LOAD Host as Host2,

  Cluster

Resident Table

Where Key = 2;

DROP Table Table;

[srchilukoori]

Hi,

Another option:

Original:

LOAD Host,

     Cluster

FROM

[https://community.qlik.com/thread/220822]

(html, codepage is 1252, embedded labels, table is @1);

Transformed:

LOAD Cluster,

     SubField(HostList, ',', 1) As Host1,

     SubField(HostList, ',', 2) As Host2;

LOAD Cluster,

     Concat(Host, ',', Host) As HostList

Resident Original

Group by Cluster;

DROP Table Original;

[rachelpurple]

it works!

Thanks very much !

[nizamsha]

TableA:

LOAD * ,Odd(ID) as Host1,

Even(ID) as Host2;

Load * Inline [

ID,Name

1,1

2,1

];

NoConcatenate

Load ID as Host3,Name Resident TableA Where Host1='-1';

Load ID as Host4,Name Resident TableA Where Host2='-1';

DROP Table  TableA;

exit SCRIPT;

[er_mohit]

Hi, Try this

Table:

LOAD// Host,

    Cluster,

    FirstValue(Host) as Host1,

    LastValue(Host) as Host2

FROM

[https://community.qlik.com/thread/220822]

(html, codepage is 1252, embedded labels, table is @1)Group by Cluster;

[sunny_talwar]

Awesome

Please close the thread if you got what you wanted by marking correct and helpful responses.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny