Transpose/Cross tab tool Configuration

[LorenzTaylor]

Good Morning ALL -

I have a set of data that needs to be Transposed or either cross tab to achieve my desired output. The current out put has 4 Columns: [Month_Year], [EQ_INIT] EQ_NR] AND [EQ_INIT_NR].

I would like to group by the [Month_Year] and each month will have a separate column with EACH [EQ_INIT_NR]. It only allows me to Concatenate, First or Last when configuring the transpose. I've provided an example below. the outlines the current output of data and the desired output of data.

[mceleavey]

Hi @LorenzTaylor ,

I'm not sure of your logic. What is the first column and why is it null aside from one cell? 

What is the grouping? As in, what granularity is each row and why are there only 9 rows in your desired output?

M.

[LorenzTaylor]

Hi @mceleavey that is for visual purposes only to clearly outline which columns need to configured. The number of rows is simply just for example purposes, In reality, each month will not have the exact amount of data. I Just want to ensure each record is represented once the data is tranposed and/or cross tabbed.

[mceleavey]

@LorenzTaylor ,

Right, I think there's some missing logic as you're asking for it to be grouped by Month_Year, but that's not what you have in the example, you have that field as the headers, and you have a Month_Year column without those values, so it's confusing.

I've put something together which is the closest I can get at the moment to matching your structure.

I've Transposed, filtered to the only category that is in your example (remove the filter and sort if you need the others) then cross-tabbed back grouping by the values, then removing the value column as the values are then in each Month_Year column.

Workflow attached.

Let me know if this helps.

M.

[LorenzTaylor]

Thanks for the assistance @mceleavey  I think I'm causing confusion by using an example that has the exact same cars. As info, this is purely an example of the format that I am trying to achieve. 

[LorenzTaylor]

In this case, I just need to be able to retrieve all the records for a given month and not Just choose from the options in the transpose tool. Concatenate, First, Last.

[OllieClarke · Accepted solution]

A small tweak to @mceleavey is to use a recordid with a group by to make a row, rather than grouping by value in the case where there are multiple instances of the same value

[mceleavey · Accepted solution]

Dammit @OllieClarke ,

I was just adding that in!

This should be correct.

M.

[binuacs · Accepted solution]

@LorenzTaylor one way of doing this with the help of tile tool 

[LorenzTaylor]

Unfortunately the workflow is causing an error when i try to download the excel file, would you mind re sending it. @OllieClarke