Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jeckstein
Partner - Creator
Partner - Creator

Making a table out of a single column

I have a table that has one column that is in this format

Header 1

Dimension1

Dimension2

Dimension3

Dimension4
Dimension5
Metric_Value_1
Metric_Value_2
Metric_Value_3
Metric_Value_4
Metric_Value_5

Dimension6

Dimension7
Dimension8
Dimension9
Dimension10
Metric_Value_6
Metric_Value_7
Metric_Value_8
Metric_Value_9
Metric_Value_10

5 dimension followed by 5 metrics I want the table to be transformed into a table that looks like this

AgeCityStateCountryZipViewsLikesUpsDownsOvers
Dimension1Dimension2Dimension3Dimension4Dimension5Metric_Value_1Metric_Value_2Metric_Value_3Metric_Value_4Metric_Value_5
Dimension6Dimension7Dimension8Dimension9Dimension10Metric_Value_5Metric_Value_6Metric_Value_7Metric_Value_8Metric_Value_9

The idea here is that there are always 5 dimension followed by 5 metrics associated with those dimension

***There is an unknown amount of rows in the column but it will be a multiple of 10

Let me know if any other information is needed.

Thanks in advance.

1 Reply
MK_QSL
MVP
MVP

Temp:

Load

  Mod(RowNo(),10) as Key,

  1 as ID,

  Header

Inline

[

  Header

  Dimension1

  Dimension2

  Dimension3

  Dimension4

  Dimension5

  Metric_Value_1

  Metric_Value_2

  Metric_Value_3

  Metric_Value_4

  Metric_Value_5

  Dimension6

  Dimension7

  Dimension8

  Dimension9

  Dimension10

  Metric_Value_6

  Metric_Value_7

  Metric_Value_8

  Metric_Value_9

  Metric_Value_10

];

Data:

Load RowNo() as Link, Header as Age Resident Temp Where Key = 1;

Left Join (Data)

Load RowNo() as Link, Header as City Resident Temp Where Key = 2;

Left Join (Data)

Load RowNo() as Link, Header as State Resident Temp Where Key = 3;

Left Join (Data)

Load RowNo() as Link, Header as Country Resident Temp Where Key = 4;

Left Join (Data)

Load RowNo() as Link, Header as Zip Resident Temp Where Key = 5;

Left Join (Data)

Load RowNo() as Link, Header as Views Resident Temp Where Key = 6;

Left Join (Data)

Load RowNo() as Link, Header as Likes Resident Temp Where Key = 7;

Left Join (Data)

Load RowNo() as Link, Header as Ups Resident Temp Where Key = 8;

Left Join (Data)

Load RowNo() as Link, Header as Downs Resident Temp Where Key = 9;

Left Join (Data)

Load RowNo() as Link, Header as Overs Resident Temp Where Key = 0;

Drop Table Temp;

Drop Field Link;