I have a table with sales information for quotes and can contain more than one row with varying quantities of the product. I first need to make the quote table to have one row for each product (so 4 rows for beans and 4 for bread) and then add a record id like thing for each record in a QID. current Q IDProductQuantityPB123Beans2PB123Beans2PB123Bread4 needed Q IDProductQ Product LevelQuantityPB123BeansPB123-11PB123BeansPB123-21PB123BeansPB123-31PB123BeansPB123-41PB123BreadPB123-51PB123BreadPB123-61PB123BreadPB123-71PB123BreadPB123-81

Producing additional rows and creating a record id

I have a table with sales information for quotes and can contain more than one row with varying quantities of the product. I first need to make the quote table to have one row for each product (so 4 rows for beans and 4 for bread) and then add a record id like thing for each record in a QID.

current

Q ID	Product	Quantity
PB123	Beans	2
PB123	Beans	2
PB123	Bread	4

needed

Q ID	Product	Q Product Level	Quantity
PB123	Beans	PB123-1	1
PB123	Beans	PB123-2	1
PB123	Beans	PB123-3	1
PB123	Beans	PB123-4	1
PB123	Bread	PB123-5	1
PB123	Bread	PB123-6	1
PB123	Bread	PB123-7	1
PB123	Bread	PB123-8	1

Developer

Accepted answers

atcodedog05

Hi @Sammy22

Here is how you can do it. I am using multi-row formula tool groupby QID to create group record id.

Workflow:

Assuming Bread and White Bread are the same.

Hope this helps : )

beans.yxmd

All comments

atcodedog05

Hi @Sammy22

Here is how you can do it.

Workflow:

1. Using gen rows to generate rows equal to quantity.

2. Using record id tool to set row id.

3. Using formula creating Q Product Level by concating Q ID and row ID.

4. Using select tool to keep only required columns.

Hope this helps : )

beans.yxmd

Ladarthure

Hi @Sammy22,

basicaly to do so you can use the generate rows which nallows to have more rows from 1 item as a quantity and then generate the ID needed. Find attached an example around this

com_id.yxmd

Emil_Kos

Hi @Sammy22,

I was a little bit slow this time 😀

Output:

New ID.yxmd

Sammy22

The problem here is there are duplicate ID eg. PB123-1. For one QID there should be unique IDs (from PB123-1 to PB123-8 in this example). I have tried to remove product from the summarize and the QID works correctly but then i will lose that product field which i need. I somehow need the original QID, product columns alongside the ID.

Emil_Kos

Hi @Sammy22,

I updated my workflow and you will have the old ID alongside the new one:

New ID2.yxmd

Sammy22

Still not quite there. I have added additional QID as that is how the data is. You can see in the outcome PB123 will have 8 unique IDs and PB345 will have 3.

Q ID	Product	Quantity
PB123	Beans	2
PB123	Beans	2
PB123	Bread	4
PB345	Milk	1
PB345	Eggs	2

outcome needed

Q ID	Product	Q Product Level	Quantity
PB123	Beans	PB123-1	1
PB123	Beans	PB123-2	1
PB123	Beans	PB123-3	1
PB123	Beans	PB123-4	1
PB123	White Bread	PB123-5	1
PB123	White Bread	PB123-6	1
PB123	White Bread	PB123-7	1
PB123	White Bread	PB123-8	1
PB345	Milk	PB345-1	1
PB345	Eggs	PB345-2	1
PB345	Eggs	PB345-3	1