Grouping data in different tables

[MadhuraBuchake]

Hello All,

I am trying to group the data by CARS category (sample data). Within CARs category I am trying to group 'issues' by orderID. This I tried with Summarize tool. However I have different issues for every OrderID and if I click on any OrderID i should have a table that displays the related issues. Is this possible?

I have CARs, Vehicle and Bike as the Categories and 8 OrderIDs. I have grouped the Issues per Order ID in table2 and I am looking for every issue ID per OrderID in a different table (I should be able to click on OrderId and then get the table3. OrderID 3 has 3 issues hence the count is 3 and description of every issue.

Table 1

Type	Order ID	Issue ID	Description
CARS	1	12345	engine broke down
CARS	2	2345	loss of oil
CARS	3	3456	noisy engine
CARS	3	4566	engine leak
CARS	3	7864	engine tear down
CARS	4	4523	window broken
CARS	4	3241	window seal
CARS	5	7457	door broken
CARS	6	3454	door hinges loose
Vehicle	7	3564	tear dowb
Bike	9	7897	Broke down

Table 2

Type	OrderID	# of Issues
CARS	1	1
CARS	2	1
CARS	3	3
CARS	4	2
CARS	5	1
CARS	6	1

Table 3

OrderID	Issue ID	Description
3	3456	noisy engine
3	4566	engine leak
3	7864	engine tear down

[gawa]

Hi @MadhuraBuchake 

I'm not sure this is the exact solution to your question, but if you want to output records in multiple sheet in excel file, then refer to attached workflow.

Depending on Type and Order ID, create file path by Formula tool, then it will split records into separate sheets.

[MadhuraBuchake]

Thank you for your reply. I am already splitting the data by categories into different sheets so I cant do that for OrderID. 

[binuacs]

@MadhuraBuchake Can you provide the expected output result for a better understanding of your use case?

[MadhuraBuchake]

Hello @binuacs Table 1 is the input and "table 2 and table 3" are the expected output. 

If this output doesn't work out then is it possible to have a table like below?

Type	OrderID	Count of Issues	Issue ID	Issue description
CARS	1	1	12345	engine broke down
CARS	2	1	2345	loss of oil
CARS	3	3	3456	noisy engine
CARS	3	3	4566	engine leak
CARS	3	3	7864	engine tear down
CARS	4	2	4523	window broken
CARS	4	2	3241	window seal
CARS	5	1	7457	door broken
CARS	6	1	3454	door hinges loose

[urvi_30 · Accepted solution]

Hi @MadhuraBuchake 

you can first use a summarize tool to get the count of issues after grouping by type and orderid, then you can join this back to your table using Type and Order Id to add the issue id and description on the side. see below! Hope this helps!

[binuacs · Accepted solution]

@MadhuraBuchake If you join the first two data sets based on the type and order id you will be getting the expected result. Is that something you are looking for?

[MadhuraBuchake]

Thank you @urvi_30 and @binuacs for your solution. This worked!!