CODEWITHSUNDEEP
qlikviewqliksense
JonJon
JonJon

Reputation: 41

Converting data from columns to rows in qlikview

I have the xlsx sheet which has data for every month in the columns. I want to convert it to rows while I load it to QlikView

First Name Last Name Home Owner Mortgage 44562 44593 44621 44652
A FSFD Y Y 34343 48768 87788 878878
DGD KJJHK Y N 5454 454 4545 74878
FDQE TERTER N N 78676 787897 454654 7787
RTE YRTYZ Y N 78634 545 4787 5744
SAS TRGFV N N 6764 54465 1215 4878

Upvotes: 0

Views: 853

Answers (1)

Stefan Stoychev
Stefan Stoychev

Reputation: 5012

In this case you can use CrossTable load.

From the documentation:

The crosstable prefix is used to turn a cross table into a straight table, that is, a wide table with many columns is turned into a tall table, with the column headings being placed into a single attribute column.

In your case we can have script like this:

Data:
CrossTable([Month], [Value], 4)
LOAD 
  [First Name], 
  [Last Name], 
  [Home Owner], 
  Mortgage, 
  [44562], 
  [44593], 
  [44621], 
  [44652]
FROM
  [C:\Users\USER\Documents\data.xlsx]
  (ooxml, embedded labels, table is Sheet1)
;

Once the script the is reloaded the resulted table will be:

data preview

CrossTable(Month, Value, 4) is the important bit. The values in the the brackets are stating that: After the 4th column, move the top row (header) as column and name the new column Month, name the values against it Value (you can see the new Month and Value columns in the screenshot). Month and Value are made-up names and the can be named whatever you want.

Upvotes: 2

Related Questions