Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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
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