LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: sopeas

INPUT

PLAN MONTH REV
A JAN 1
A FEB 2
A MAR 3
B JAN 1
B FEB 2
B MAR 3

DESIRED OUTPUT

PLAN JAN FEB MAR
A 1 2 3
B 1 2 3

Can anyone please help?

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: johnpelz

You should use the "Rotate" node in the Core library under the "Metadata" category.

Use the following configuration in the Rotate node to achieve the results you are looking for:

NameField: MONTH
ValueField: REV
PivotField: PLAN

See attached example as well.

Enjoy
Attachments:
Pivot_Example.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: sopeas

Thanks for your response.

I've tried using the rotate node but it only works if there is a constant number of rows per plan. For example it doesnt work for the following example.

PLAN MONTH REV
A JAN 1
A FEB 2
B JAN 1
B FEB 2
B MAR 3

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: rabbott

Here are two options:

1. Set the Rotate node IgnoreExtraNames parameter to "true" (default is false). This will ignore any new values found in the NameField after the first group has been processed, i.e. the first group sets the NameField value for the remainder of the input. In the above example, "B,MAR,3" would be ignored.

2. The attached graph example will group the input by the PivotField (PLAN) and count the number of rows in each group (rows). It will then sort the output in reverse order from greatest to least, separate the data matching the PivotField with the greatest number of rows, and then place this data set first amongst the original data input. The Rotate node is then applied, adding a value of "none" where the number of rows in the group is less than that in the first group.

Hope one of these solutions will work for you.
Attachments:
Cross_Tabulation_Sort.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: roeperg

Hi

I had a similar problem with Rotate, namely that some data was dropped if it was unique. Basically any pivot columns that are not included in the first row of data disappear. I have attached a solution to the problem which is basically a cartesian join to ensure all permutations are present at the Rotate input pin.

Greg
Attachments:
New_Cross_Tabulation_Sort.brg