13 Replies Latest reply on Jan 17, 2017 7:39 AM by Joe Oppelt

# How to create multiple rows of data from one row for aggregation?

I have an odd situation where I need to create "multiple records" from one record. I hope I can explain this…

In Tableau, I have joined two Excel tables on the field ‘Piece’.  Each Piece can have multiple Components and

number of Components. This is what I’m trying to aggregate. However, when a Piece equals a star (*), then

one half of the Components are from one Piece, with the other half from another Piece. In the Example data

below, half of the Components are from Piece A50, with the second half from Piece A64.

What Piece to assign is determined by the Column/Row of the * record. In my example, the * is in Column 1/Row 4.

This means I need to somehow lookup the Piece and Piece Qty from Column 1/Row 3 and Column 1/Row 5. See

the image below. Can this be done in Tableau?

I’ve attached a workbook to help. As you can see by the results below, I can aggregate everything properly when

Piece is not a star. The highlights indicate where I need help to do some tableau magic. Any help is appreciated.

• ###### 1. Re: How to create multiple rows of data from one row for aggregation?

Hi David,

I think you need to re-structure your data.  Instead of having

Type          Piece     Qty

CE4A2R4     *         8

do

Type            Piece     Qty

CE4A2R4     A64      4

CE4A2R4     A50      4

• ###### 2. Re: How to create multiple rows of data from one row for aggregation?

I agree with you, unfortunately I don't have control over the data.

This is the output format I'm forced to use.

• ###### 3. Re: How to create multiple rows of data from one row for aggregation?

Hi David

I switched the tables

• ###### 4. Re: How to create multiple rows of data from one row for aggregation?

See attached.  No switching tables.  Just use LOOKUP

• ###### 5. Re: How to create multiple rows of data from one row for aggregation?

What I did in there:

First, to help me see that I'm addressing the order of things properly, I made a calc called INDEX and added it to the viz.  (See Sheet 3).  I also added Row and Column to the viz, because that's what you'll need to walk through the table properly.

Then I changed the table calc settings so that I forced the INDEX value to run from 1-N for the rows, restarting within each column.

(One drawback here is that I have only one [Type] value and one [Column] in the data set you gave, so I can't test for sure that the "Restarting Every" is actually restarting for each Column.  Add a second [Type] with two columns.  Leave "CE4A2R4" as it is.  Make the new [Type] alphabetically after the current one.  This will let me test out that it resets both for a new [Type] and for a new [Column].  What I described for additional data will leave the column value at 1 for the first two "chunks" of data, and that will be the ultimate test for this.)

This test lets me see how Tableau "sees" the data.  I want to get this part right so that I know I'm getting the true prior and next cells for my LOOKUP() functions.

Then I created LOOKUPs for Prior Piece, Next Piece, and Next Piece Quantity.  Using offset -1 looks at the prior cell.  Using 1 looks at the next.  I set the table calc settings for these calcs the same as I do for the INDEX calc.

Once we get this solidified with fuller test data, we can then address the way this caused the actual Piece Counts and Total Components to get split up.

• ###### 6. Re: How to create multiple rows of data from one row for aggregation?

I have used the lookup function before but can't say I completely understand what it does.

I see you were able to identify the Prior/Next Pieces, which is great. However, the Total Components by Row in your attachment still doesn't have a Qty for Row 4. Do you need more data for things to work?

• ###### 7. Re: How to create multiple rows of data from one row for aggregation?

LOOKUP() says to grab whatever field you tell it to grab, from the row specified by the offset value.  -1 says to get the prior row.  +1 says to get the next.

This is a table calc, so we can mess around with the table settings of the calc to tell Tableau what "road" to follow when looking forward/backward.  In this case I want to tell it to look to the prior row#.  (Or next, as the case may be.)

I haven't messed with total components yet.  Nor the Qty.  I just took the first step for now, identifying the next/prior piece.  But I'm still not sure I actually have the precisely correct table calc setting because I can't test it out with the limited data we currently have.  I need to make sure the index restarts at 1 when either the Type changes or the column number changes.  That's why I asked for a little more data.

• ###### 8. Re: How to create multiple rows of data from one row for aggregation?

Question:  Can a "*"  [Piece] ever be the first or last row number?  If so, what do you want to use in those cases?

• ###### 9. Re: How to create multiple rows of data from one row for aggregation?

I have attached an Excel file with extended data containing multiple Columns.

And yes, there could be instances where the Top/Bottom are *. I don't have an answer to the plug value other than to suggest a parameter value when there isn't a row above/below.

Also, what if the data isn't sorted like I've shown. Will the lookup always go up/down one row or is it smart enough to find the correct Previous/Next row? Ex: 4-1 = 3, so find Column #/Row 3

• ###### 10. Re: How to create multiple rows of data from one row for aggregation?

See attached.  I think I built the join the way you did for your first data source: (left join.  Piece=piece).  But now I'm getting multiple pieces per row#.  And that is messing up the calcs.  What should be the proper setup here?

But meh.  What I did was "View Data" and dumped your original into excel.  Then I just duplicated it a bit and made what I was looking for.  Now I'm just using "Sheet1" data source.

So I had to modify the table calcs slightly.  Not only do I have to say "restart every [Column #], but also specify "at the level of [Row#].

I see that I got sloppy in my cut-and-pasting in excel to duplicate  data, and the "Part2" rows for "XXX" ended up in "CE4a", but that doesn't change what I'm doing here...

So I have the LOOKUPs working properly.  I did a [Piece quantity revamped] calc to display the new piece qty for "*".  (Again, the two ""Part 2" rows are impacted by my data.  But that's not the issue here.)

Comp Qty is still blank.  That looks like something that should come out of the data source, no?

I modified [Total Components] to handle "*".  But since nothing is in [Comp Qty], it results in null.

• ###### 11. Re: How to create multiple rows of data from one row for aggregation?

As for sorting, regardless of how your data comes in from the data source, you can sort ascending.

Offsets work on however the data is sitting in the table, so you'll want to sort your [Row#].

• ###### 12. Re: How to create multiple rows of data from one row for aggregation?

Joe, you raised a good question about the top/bottom items having a '*'. This will happen often, so the lookup won't be the final solution. While the lookup does seem to be working, I'm going back to the source and recommending changes to the data structure. Thanks for you time looking at my problem.

• ###### 13. Re: How to create multiple rows of data from one row for aggregation?

The way I did this will allow you to detect if your "*" is the first or last in the list, and then you can so something specific in those cases.  So it's not the end of the world.  But yes, you need to figure out what you want to do for those circumstances.