4 Replies Latest reply on Jan 17, 2019 4:12 AM by Zhouyi Zhang

Combine multiple order rows to 1

willem.brachel.0 Jan 16, 2019 4:17 AM

Hi all,

I am trying to configure our database to correctly measure cross sell items, but unfortunately I don't really know how to do it.

For example, this is how our order output looks like:

Order	Customer	Email	Category	product	Price
9844	John	John@doe.com	Bikes	Bike	899
9844	John	John@doe.com	Accessories	Helmet	199
9844	John	John@doe.com	Clothes	Shoes	166

In this example Bike is the main product, and accessories and clothes are cross sell items, I would like to show these cross sell items on the same row as the bike:

Something like; IF category = Accessories then Accessories

Order	Customer	Email	Category	product	Cross sell1	Cross Sell2
9844	John	John@doe.com	Bikes	Bike	Accessories	Clothes
9844	John	John@doe.com	Accessories	Helmet	Accessories	Clothes
9844	John	John@doe.com	Clothes	Shoes	Accessories	Clothes

Someone knows how to achieve this?

1. Re: Combine multiple order rows to 1

Zhouyi Zhang Jan 16, 2019 4:59 AM (in response to willem.brachel.0)

Hi, Willem

Not quite sure whether you would like a hard code for cross items or dynamically, and also how to determine which category is the main product. but below is some idea of how to populate the data as cross join.

use inner join your data to itself with key 1=1 as shown below
And the view should be something like this

Hope this helps

ZZ
Like Show 0 Likes(0)

Actions
2. Re: Combine multiple order rows to 1

albert wong Jan 16, 2019 5:01 AM (in response to willem.brachel.0)

Hi Willem,

Can you post an Excel example of what your end result should look like?
Like Show 0 Likes(0)

Actions
3. Re: Combine multiple order rows to 1

willem.brachel.0 Jan 16, 2019 6:34 AM (in response to Zhouyi Zhang)
Hi ZZ and Albert,

Actually, this is almost exactly what I would like to achieve. I did write it down a bit messy, I am sorry.

What I would like to achieve: that the Category column will be transferred to rows, see attached a example of what I would like to achieve.

testcrosssell.xlsx 9.9 KB
Like Show 0 Likes(0)

Actions
4. Re: Combine multiple order rows to 1

Zhouyi Zhang Jan 17, 2019 4:12 AM (in response to willem.brachel.0)

Hi, Willem
With my solution, you just need to drag the category(sheet1) into text as shown below to achieve what you expected.

Hope this helps
ZZ
Like Show 0 Likes(0)

Actions