Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

How to make multiple rows into one row in multiple columns

I am new in Qlik Sense. I am learning how to make multiple rows into one row in multiple columns for a report. For example,

ID     Sale Date       Product         Price

A      01-02-2017     book              50

A      01-02-2017     computer        500

B      02-01-2017     Phone            60

B      02-01-2017     cup                10

I want to merge the rows like this.

ID     Sale Date       Product1        Price1     Product2     Price2

A      01-02-2017     book              50           Computer    500

B      02-01-2017     Phone            60           cup             10

Any help always appreciate. Thanks in advance!

2 Replies
Vegar
Not applicable

Re: How to make multiple rows into one row in multiple columns

I have no quick fix for you, but why do you want this? It can end up weird if you can have Various amount of products in your orders. If you have one ID with many products and the rest with  few then uou will end up with a table with many columns and empty cells.

Consider if a pivot table of the original data source could meet your demands, it is an easier to accomplish and more dynamic approach to the visualisation.

Good luck

Please ekskuse my Norglish and Swenglish typos.
rwunderlich
Not applicable

Re: How to make multiple rows into one row in multiple columns

You can do this at the script level with the Generic Load prefix. See http://qlikviewcookbook.com/2010/05/use-cases-for-generic-load/

qvf and qvw examples attached. Script for your data would look like:

Data:

LOAD * INLINE [

ID, Sale Date,  Product, Price

A,  01-02-2017, book,    50

A,  01-02-2017, computer, 500

B,  02-01-2017, Phone,    60

B,  02-01-2017, cup,      10

];

Pivoted:

Generic LOAD

  ID,

  'Product' & AutoNumber(RecNo(),ID),

  Product

Resident Data;

Pivoted:

Generic LOAD

  ID,

  'Price' & AutoNumber(RecNo(),ID),

  Price

Resident Data;


If you want to re-assemble the generic tables back into the main data table, use the script loop shown at the end of the blog post. Or use the Qlikview Components  CALL Qvc.JoinGenericTables('Data', 'Pivoted');


I would second Vegar's caution.  They may be a simpler approach to your ultimate goal.


-Rob

http://masterssummit.com

http://qlikviewcookbook.com