Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
New Contributor III

Data transformation: Rows to Columns

Hi all!  I have a spreadsheet of reimbursements- where every transaction has it's own row (see below):

John Smith$10.001-2-15
John Smith$5.001-3-15
Jane Duncan$15.001-4-15
Jane Duncan$15.001-5-15
Jane Duncan$25.001-6-15

I'd like to combine the data so each person gets their own row, and every amount and date for that person becomes it's own column:

NameAmount (1)Date (1)Amount (2)Date (2)Amount (3)Date (3)
John Smith$10.001-2-15$5.001-3-15
Jane Duncan$15.001-4-15$15.001-5-15$25.001-6-15

How might I transform the data in the load script to accomplish this?


3 Replies
Esteemed Contributor III

Re: Data transformation: Rows to Columns

use cross table

Re: Data transformation: Rows to Columns

I think you would need Generic Load here: The Generic Load


LOAD Name,

  SubField(Amt, '|', 1) as Amt1,

  SubField(Amt, '|', 2) as Amt2,

  SubField(Amt, '|', 3) as Amt3,

  SubField(Date, '|', 1) as Date1,

  SubField(Date, '|', 2) as Date2,

  SubField(Date, '|', 3) as Date3;

LOAD Name,

     Concat(Amount, '|', RecNo()) as Amt,

     Concat(Date, '|', RecNo()) as Date



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

Group By Name;


Honored Contributor II

Re: Data transformation: Rows to Columns

could you not just use the pivot table object to display the content that way?  Altering how you load your data for visualisation purposes beyond the standard fact vs dimension model discussions seems the wrong approach unless I'm missing something.