How to make multiple rows into one row in multiple columns

[#1]

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!

[Vegar]

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

[rwunderlich]

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

[Mahamed_Qlik]

Hi

We have simillar case to I have modified the data as per our requirnment as below:

Data:

LOAD * INLINE [

ID, Sale Date, Product, Price

A, 01-02-2017, book, 50

B, 02-01-2017, Phone, 60

B, 02-01-2017, cup, 10

A, 01-02-2017, computer, 200

];

I have added one more row highlighted (green)

but In our case we want to create product categories as product1,product2, etc.

We are getting below output:

ID     Product1   Product2  Product3

A      book           book          computer

B      phone        cup            -

'Book' is repeated in product1 and product2