LAE

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

Originally posted by: johnpelz

I'm attempting to rotate data in the following format:

ID Audit
100 AA
100 BB
100 CC
200 AA
200 CC
200 DD
300 AA
300 EE

To

ID AA BB CC DD EE
100 1 1 1 0 0
200 1 0 1 1 0
300 1 0 0 0 1

The Core rotate node does not support this because the data is not consistent within its grouping (Pivotfield of ID).

I put together a graph to get around this by adding the same set of fields to each ID -- so ensuring a record for AA, BB, CC, DD, EE exist for each ID.

The rotate node is now failing due to what I believe is a bug; HOWEVER, I feel as though I might be over-complicating this and thought I'd solicit some feedback / suggestions from the BRAIN Forum "experts".

Any thoughts on another way to slice this?

I'll probably end up writing a custom python node to handle it if not.

Thanks
Attachments:
Rotate.zip

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

Originally posted by: mmarinelli

I believe that you'll either have to code a Python node or get the Rotate node fixed, because your requirement to have variable metadata won't work with regular BRAIN nodes. If you always had a fixed set of values in the audit column, you could code this in the graph at design time, but I don't believe that's workable otherwise. Anybody else want to weigh in here?

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

Originally posted by: rboccuzzi

You have the correct idea, the rotate node needs to know what value to put in each of the different fields for each pivot group. It might be a nice enhancement to have it put in a NULL (or some specified default value) if a field doesn't get a value for a given pivot group.

For your example above, you of course need to add the value field, all you are showing is the pivot field and the name field. When you add the value field and set it to 1 or 0, that is the full set of what you need.

In your graph, you are doing everything correctly (or almost everything The graph is failing because the original data has multiple rows for a given MDN, so when you join them with a constant value to the fixed set of AuditCodes, you end up with duplicates you don't want. This is what is breaking rotate, because you will end up for these MDNs having multiple "sets" of audit codes, so too many in a pivot group.

Once you remove these dups, it works. Not sure exactly what you are trying to do, but the easiest way might be to sort on MDN, AuditCode2, and Value (so all the 0's in the value are first, as you want), and then to sort uniquely (and stablely! if you don't sort stable, it won't reliably work!) on MDN, AuditCode2. That seemed to do what you wanted.

Cheers
$Rich<br><br>Post edited by: rboccuzzi, at: 2008/06/04 17:55

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

Originally posted by: johnpelz

You are correct, sir! Fantastic.

I actually need to use an agg on MDN, AUDITCODE2 in order to preserve the correct "VALUE" indicator. "0" is the default (meaning no issue) but a value of "1" needs to be preserved if it exists across a single MDN, AUDITCODE2 combination. At the end of the day, the "1" value for a particular AuditCode field is what is important to the client --- they add it up across MDNs to get a numeric result.

Anyways - attached is the updated approach.

You know what would be really nice?!? If the rotate node handled all of this itself. Of course, you'd probably have to parse the data twice, once to establish all the possible fields, and a second time to fill in the details for each PivotFiled -- filling in null where it didnt exist.

Perhaps another day ....

Thanks again

Attachments:
Rotate_Example.brg

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

Originally posted by: johnpelz

You are correct, sir! Fantastic.

I actually need to use an agg on MDN, AUDITCODE2 in order to preserve the correct "VALUE" indicator. "0" is the default (meaning no issue) but a value of "1" needs to be preserved if it exists across a single MDN, AUDITCODE2 combination. At the end of the day, the "1" value for a particular AuditCode field is what is important to the client --- they add it up across MDNs to get a numeric result.

Anyways - attached is the updated approach.

You know what would be really nice?!? If the rotate node handled all of this itself. Of course, you'd probably have to parse the data twice, once to establish all the possible fields, and a second time to fill in the details for each PivotFiled -- filling in null where it didnt exist.

Perhaps another day ....

Thanks again

Attachments:
Rotate_Example-b542a1c206df52448a213caddb3e3d95.brg