Qlik Community

Qlik Sense App Development

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

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
kalmirall
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):

NameAmountDate
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?

Thanks!
Katie

3 Replies
Chanty4u
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


Table:

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

FROM

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

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

Group By Name;

Capture.PNG

ogster1974
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.