topic Columns to rows based on naming convention in QlikView

Columns to rows based on naming convention

Fri, 10 Jul 2015 07:50:21 GMT

Hello community,

I was given below SQL table data (I know it's not in NF ).

Instead of columns I want to create rows so that I have three columns width, length, height.

The number behind each column indicates their relationship.

Example:

Width1, Length1 and Height1 are related

Width2, Length2 and Height2 are related and so on.

The actual DB-table contains plenty of columns so here's just an extract:

In the end I want a table like so:

ID | Pos | Width | Length | Height

1 | 1 | 10 | 2 | 10

1 | 2 | 15 | 4 | 20

1 | 3 | 20 | 6 | 30

2 | 1 | 20 | 5 | 1

2 | 2 | 40 | 10 | 2

2 | 3 | 60 | 15 | 3

Here's the data:

TableInit:

LOAD * INLINE [

ID, Width1, Width2, Width3, Length1, Length2, Length3, Height1, Height2, Height3

1, 10, 15, 20, 2, 4, 6, 10, 20, 30

2, 20, 40, 60, 5, 10, 15, 1, 2, 3

];

TableInit2:

Load ID,

SubField(Width1&','&Width2&','&Width3,',') AS Width

,SubField(Length1&','&Length2&','&Length3,',') AS Length

,SubField(Height1&','&Height2&','&Height3,',') AS Height

Resident TableInit;

Thank you.

Re: Columns to rows based on naming convention

jagan — Fri, 10 Jul 2015 08:08:31 GMT

Hi,

Try this script

Temp:

CrossTable(Type, Value)

LOAD * INLINE [

ID, Width1, Width2, Width3, Length1, Length2, Length3, Height1, Height2, Height3

1, 10, 15, 20, 2, 4, 6, 10, 20, 30

2, 20, 40, 60, 5, 10, 15, 1, 2, 3

];

Data:

LOAD

ID,

Replace(Type, 'Width', '') AS Pos,

Value AS Width

RESIDENT Temp

WHERE Type Like 'Width*';

OUTER JOIN (Data)

LOAD

ID,

Replace(Type, 'Length', '') AS Pos,

Value AS Length

RESIDENT Temp

WHERE Type Like 'Length*' ;

OUTER JOIN (Data)

LOAD

ID,

Replace(Type, 'Height', '') AS Pos,

Value AS Height

RESIDENT Temp

WHERE Type Like 'Height*' ;

DROP TABLE Temp;

Regards,

Jagan.

Re: Columns to rows based on naming convention

Fri, 10 Jul 2015 12:51:05 GMT

beautiful. Thank you.