Use of Pivot Table When a Column Contains Many Variables

jgRugby_123098

You may have run accross this when dealing with FedEx or UPS parcel data. An invoice can have 100's of packages, and for each package you can have many individual charges - e.g. charge, discount (negative), fuel, residential, over dimension, etc. There are many of these, and they are adding to them all the time.

I want to have this all in one row. The four fields are: 1) Invoice number 2) Detail Line - really is a number used to connect to a package - 3) Amount of the "charge" 4) Description of the charge.

I want to the row to looking like:

(1) Invoice (2) Detail Line number (3) Amount (4) Description (3) Amt (4) Desc (3) Amt (4) Desc

One invoice, one detail line, multiple amounts and description - all in one row.

I've attached a MS Work Doc showing the basics of the ETL.

The descriptions in "4" are unlimited and can change. I want the pivot to handle this.

Thank you for your help.

domo pivot.docx

MarkSnodgrass

You would use the pivot tile in Magic ETL to do this. However, it requires you to manually name the columns and doesn't dynamically name the column based on the data.

For example, the Pivot tile converts data in this format...

... to data in this format...

... using this configuration:

Here's the KB article about it.

https://domo-support.domo.com/s/article/360044951294?language=en_US

This will require you to update the ETL when new descriptions are created that you haven't yet accounted for. A way to manage this would be to create a card that has all the combinations and then alerts you when a new description is added.

jgRugby_123098

Mark - I do appreciate the response. It just seems to me something is missing in the ETL process. I use another ETL tool for ad hoc reporting, and it dynamically adds the columns.

I'm afraid the solution to this is to have many more rows than are needed. Maybe no other choice.

Thank you,

jgRugby_123098

I studied this some more, and I came up with a solution. It is a little funky, but it worked.

Sorry if I don't describe this like a master would.

1st - changed the dollar amount to a string.

2nd - grouped the description and the strings using the "GROUP BY" function under "Aggregate". This cut me down to 1 row for each identified line.

3rd - I used the "SPLIT COLUMN" function on descriptoin and charge columns. I named the columns for the description "DESC 1", DESC 2" through 10; and the charge amount "DESC 1 CHARGE" (through 10). I maked the split to put anything left over in the last column. I tested out and added on column more than what was being parsed out. This give me room should a 10the description pop up, and retains the data so I can see how many more columns may be needed - should this happen.

4th - I use the Select Columns to pull the final group in, and then moved the charge column right next to the charge descrition.

Not real smooth, but it got me to where I wanted to be.