Transpose rows to columns

[Former Member]

Hi all,

-

---

The messy problem -

---

I have an XML document whose rows contain production order operations.

<row><order>123</order><op>10</op><material>1000017</material></row>

<row><order>456</order><op>10</op><material>1100345</material></row>

<row><order>456</order><op>60</op><material>1100345</material></row>

I have another XML document whose rows contain characteristics of the production order material (variant configurator).

<row><order>123</order><char>NAME</char><value>Chair</value></row>

<row><order>123</order><char>SIZE</char><value>15</value></row>

<row><order>456</order><char>COLOR</char><value>Red</value></row>

<row><order>456</order><char>SIZE</char><value>40</value></row>

Each production order in the characteristic list may have a different set of characteristics (<char> values from associated <row>s).

I don't know what that set of characteristics may be, nor how many distinct <char> values there will be. (This is a point for compromise though)

I want elements added to the production order operation list for each possible characteristic,

then fill in the values as appropriate for that production order operation.

I.E. I want the list of distinct <char>s to become elements in the production order operation document

and the relevant <value>, if any, as the value.

<row><order>123</order><op>10</op><material>1000017</material><NAME>Chair</NAME><SIZE>15</SIZE><COLOR></COLOR></row>

<row><order>456</order><op>10</op><material>1100345</material><NAME></NAME><SIZE>40</SIZE><COLOR>Red</COLOR></row>

<row><order>456</order><op>60</op><material>1100345</material><NAME></NAME><SIZE>40</SIZE><COLOR>Red</COLOR></row>

-

---

The question-----

How would you do it?

--Amy Smith

--Haworth

[Former Member · Accepted solution]

The output xml from the transaction could alternatively look something like this.

Can we nest values? (I don't see a way to define the OutputXML as anything but a flat structure.)

Then the xsl could easily turn those <chars> into <td> tags for the data table. Key is that each row has the same

list of <char>s in the same order. Easily done...

<row>

<order>123</order><op>10</op><material>1000017</material>

<chars>

<char>NAME</char><value>Chair</value>

<char>SIZE</char><value>15</value>

<char>COLOR</char><value></value>

</chars>

</row>

etc.

Hmmm --- Could <chars> be defined as a character string to MII in the OutputXML definition

and then put the string in there <char>.... Then when xsl runs it will be able to do a for each on <char> elements?

--Amy Smith

--Haworth

Edited by: Amy Smith on Aug 18, 2009 4:37 PM

[Former Member · Accepted solution]

Hi, Amy.

I'd probably use BLS and a few repeaters to do this.

First, I'd define an MII document with the "base" or "common" columns in it.

Then, I'd use the "distinct" action to extract a list of the characteristics. I'd loop through this list, and use the MII column definition action to "add" more columns to the base document.

Then, I'd loop through the "orders" rowset, using the MII "Row" action to assign the "common" column values.

Inside that loop, I'd loop through the "characteristics" rowset (using dynamic Xpath and the order # to select a subset of rows for the repeater), and use the MII "DataItem" action to assign them (the cool feature of this action is that you can specify the column name and value as links).

That should work perfectly.

Rick

[Former Member · Accepted solution]

One other comment: My approach presumes that the characteristic names are all legitimate XML element names. If not, you'll need to use the XmlEncodeName function (or something like that) to define the pseudo-columns.

Bottom line is that it is very do-able without the mess of XSL.

[Former Member · Accepted solution]

Amy Smith,

I don't think you have readymade action block for this. You might build a small code:

Local var newChar

type: xml

value: <chars> <char/> <value/> </chars>

Local var newOrd

type xml

value: <row><order></order><op></op><material></material></row>

Loop thru orders assign order, op, material to newOrd

within that loop thru mat characterists relevant to that order material

assign each char & value to newChar structure & append the structure to newOrd

append newOrd to OutputXML after inner loop, then Initialize above variables

I could get this:

<order>123</order><op>10</op><material>1000017</material>

<chars>

<char>NAME</char>

<value>Chair</value>

</chars><chars>

<char>SIZE</char>

<value>15</value>

</chars></row><row>

<order>456</order><op>10</op><material>1100345</material>

<chars>

<char>COLOR</char>

<value>Red</value>

</chars><chars>

<char>SIZE</char>

<value>40</value>

</chars></row><row>

<order>456</order><op>60</op><material>1100345</material>

<chars>

<char>COLOR</char>

<value>Red</value>

</chars><chars>

<char>SIZE</char>

<value>40</value>

</chars></row></Columns>

BR,

SB

[Former Member · Accepted solution]

Thanks Rick,

(I tested this using for/next instead of the real data just to test the technique of dynamic columns.)

I set the Name of the Column Action Block to Local.colName which held the column name. (Test1,Test2,...)

I set the the Name of the DataItem Action Block to Local.colName and the Value to Next_Col_Num.CurrentItem.

Well, I DID get my 10 columns and data in each one. However, see below!

LOL -- What is the syntax for the Name and Value when these are "links"?

I tried { } and [ ] and the manual doesn't even mention this option!

--Amy Smith

--Haworth

<Row>

<ORD>1002</ORD>

<OP>102</OP>

<Local.colName>Next_Col_Num.CurrentItem</Local.colName>

<Local.colName>Next_Col_Num.CurrentItem</Local.colName>

<Local.colName>Next_Col_Num.CurrentItem</Local.colName>

<Local.colName>Next_Col_Num.CurrentItem</Local.colName>

<Local.colName>Next_Col_Num.CurrentItem</Local.colName>

<Local.colName>Next_Col_Num.CurrentItem</Local.colName>

<Local.colName>Next_Col_Num.CurrentItem</Local.colName>

<Local.colName>Next_Col_Num.CurrentItem</Local.colName>

<Local.colName>Next_Col_Num.CurrentItem</Local.colName>

<Local.colName>Next_Col_Num.CurrentItem</Local.colName>

</Row>

[Former Member · Accepted solution]

NEVER MIND --- I got it... Thanks Rick.