LAE

Welcome to the LAE community!  Please feel free to start a discussion in the discussion tab or join in a conversation.

Discussions

Members

Resources

Events

 View Only

  • 1.  Table transposition with Pivot - Data to Names ?

    Employee
    Posted 12-23-2014 06:07

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

    Originally posted by: Denis Drouais

    Hello,

    I have the following table that I would like to transpose (potentially with the Pivot - Data to Names node) :

    Month, Reference 1, Reference 2, Reference 3, Reference 4
    January, 15, 20, 2, null
    February, 20, 25, 5, 3
    March, 30, 15, 1, 5

    into :
    Reference, January, February, March
    Reference 1, 15, 20, 30
    Reference 2, 20, 25, 15
    Reference 3, 2, 5, 1
    Reference 4, null, 3, 5

    I think I can use the Pivot - Data to Names node but I have to enter two parameters, NamesField and DataFields :
    In NamesField I have specified Month. As I could not let it blank, I tried Reference 1 in DataFields. It gives me :

    Reference 2, Reference 3, Reference 4, January, February, March
    20, 2, null, 15, 20, 30

    I think that I am close to the end result but can not get it.

    Can somebody help me with it ?

    Thank you very much in advance,

    Best regards
    Denis


  • 2.  RE: Table transposition with Pivot - Data to Names ?

    Employee
    Posted 12-23-2014 07:15

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

    Originally posted by: ryeh

    Try the attached.
    Attachments:
    transposeMatrix.brg


  • 3.  RE: Table transposition with Pivot - Data to Names ?

    Employee
    Posted 12-26-2014 07:23

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

    Originally posted by: Denis Drouais

    Thank you very much. It helps a lot.

    In real life, though, the "Reference X" columns have very different names, so that PivotFields = Reference* in Names to Data node does not work.

    Is there a syntax for saying "every colomn exception the one named 'Month'" ?


  • 4.  RE: Table transposition with Pivot - Data to Names ?

    Employee
    Posted 12-29-2014 22:25

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

    Originally posted by: ryeh

    Unfortunately, I don't think the PivotFields field can accommodate pattern exclusions. A work-around is to pivot all the fields, and then search for records where the Month appears and create a new field with the appropriate month value. To make sure a month value is always included, I would make sure that Month is the first field. In case it isn't, you could always use a Filter node and emit Month first.
    Attachments:
    transposeMatrix1.brg