Producing additional rows and creating a record id

[Sammy22]

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

[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 : )

[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

[Emil_Kos]

Hi @Sammy22,

I was a little bit slow this time 😀

Output:

[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.

[atcodedog05]

Hi @Sammy22 

You can provide us a sample data, sample output and the logic. We might be able to help you out.

[Emil_Kos]

Hi @Sammy22,

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

[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

[atcodedog05 · Accepted solution]

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 : )

[Emil_Kos]

Hi @Sammy22,

I have created an alternative solution to @atcodedog05. I used the running total instead of the multi-row formula. 

The output: