Is the tile tool the best tool to pivot and aggregate column groups

[Sunseeray]

I have a data set that has a grouping of columns by date for each record. There are 6 dates and 8 columns in each group. I am needing to consolidate the columns by date for each record and am trying the Tile tool but am finding it tricky.

Below is the general current and desired data structure.

Current Structure

RecordID

Date1

Bucket1

Bucket2

Bucket3

Bucket4

Bucket5

Bucket6

Bucket7

Bucket8

Date2

Bucket1

Bucket2

Bucket3

1

2

3

4

5

Desired Structure

RecordID

Date

Bucket1

Bucket2

Bucket3

Bucket4

Bucket5

Bucket6

Bucket7

Bucket8

1

Date1

1

Date2

1

Date3

1

Date4

1

Date5

1

Date6

2

Date1

2

Date2

2

Date3

2

Date4

2

Date5

2

Date6

Thanks in advance!

UPDATE: I did not describe what was in my data and I apologize. All columns, with the exception of the RecordID are numeric values. The purpose of the analysis is to aggregate inputs to each bucket per record from date to date. This would need an additional column for the amount by date.

Current Structure
Record ID	Amt - Date1	Bucket1	Bucket2	Bucket3	Bucket4	Bucket5	Bucket6	Bucket7	Bucket8	Amt - Date2	Bucket1	Bucket2	Bucket3	Bucket4	Bucket5	Bucket6
12345	50,000.00	20.00	-500.00	25.00	0.00	0.00	0.00	0.00	0.00	49545.00	0.00	0.00	0.00	500.00	0.00	0.00
55555	20000	0.00	0.00	0.00	0.00	0.00	0.00	15.00	0.00	20015.00	0.00	0.00	0.00	0.00	0.00	80.00
66666	50	0.00	-10.00	750.00	0.00	0.00	0.00	0.00	0.00	790.00	0.00	0.00	0.00	0.00	0.00	0.00
Desired Structure
Record ID	Date	Amount by Date	Bucket1	Bucket2	Bucket3	Bucket4	Bucket5	Bucket6	Bucket7	Bucket8
12345	Amt - Date1	50000.00	20.00	-500.00	25.00	0.00	0.00	0.00	0.00	0.00
12345	Amt - Date2	49545.00	0.00	0.00	0.00	500.00	0.00	0.00	0.00	0.00
12345	Amt - Date3	50045.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
12345	Amt - Date4	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
12345	Amt - Date5	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
12345	Amt - Date6	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
55555	Amt - Date1	20000.00	0.00	0.00	0.00	0.00	0.00	0.00	15.00	0.00
55555	Amt - Date2	20015.00	0.00	0.00	0.00	0.00	0.00	80.00	0.00	0.00
55555	Amt - Date3	20095.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
55555	Amt - Date4	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
55555	Amt - Date5	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
55555	Amt - Date6	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
66666	Amt - Date1	50.00	0.00	-10.00	750.00	0.00	0.00	0.00	0.00	0.00
66666	Amt - Date2	790.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	5000.00
66666	Amt - Date3	5790.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
66666	Amt - Date4	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
66666	Amt - Date5	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
66666	Amt - Date6	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00

[FinnCharlton]

Hi @Sunseeray , I don't think the tile tool is right for the job, you can accomplish the same thing with some multi-rows. Can't upload screenshots at the moment but check out the example workflow attached. Hope this helps!

[Sunseeray]

Thank you @FinnCharlton for responding and providing a better solution!

[Sunseeray]

Hi @FinnCharlton, I needed to update my post. I didn't describe the data well enough. Each of the dates are columns of aggregated values of the previous 8 buckets. This would need an additional column for the value after taking the date from the column name. I included a second example that shows this structure.

[ArtApa · Accepted solution]

Hi @Sunseeray - I've tried to build a dynamic solution for you: 

[Sunseeray]

Hi @ArtApa! Thanks so much

[ArtApa · Accepted solution]

@Sunseeray - Is this something that you wanted? Is this a solution? 

[Sunseeray]

Hi @ArtApa, this gets me very close to what I need and I should be able to get it there. Thank you very much!