We recommend switching to the latest versions of Edge, Firefox, Chrome or Safari. Using Internet Explorer will result in a loss of website functionality.

Transpose columns to rows

Comments

8 comments

  • Avatar
    Karlis Majevskis

    Actually my source data looked like this (2 columns):

    name type
    bob a/b/c
    mary x/y
    john x/y/z/f

    I've converted it to the table above with Expand from list node. So i need a solution for either first table or this one.

    Thanks a lot

    0
    Comment actions Permalink
  • Avatar
    Gerry Mullin

    The logic I went with was using the first table you posted. The idea is to create the new field name as a value first (type1, type2 etc.), then to use Pivot - Data to Names node to pivot those to columns.

     

    Attached files

    Pivot Columns to Rows.lna

     

    1
    Comment actions Permalink
  • Avatar
    Karlis Majevskis

    Hey, thanks. Good idea. But how i can add those value fields type1, type2, etc.? 

    0
    Comment actions Permalink
  • Avatar
    Karlis Majevskis

    Oops! Sorry. Did not notice lna file attached. Will check it out and come back later.

    0
    Comment actions Permalink
  • Avatar
    Karlis Majevskis

    It worked! Thanks a lot!

    0
    Comment actions Permalink
  • Avatar
    Tevita Fainga

    Gerry, I have some kind of issue seem  similar with Karlis but my situation is not yet resolved and I'm still trying to find a solution for it and I believe this is due to the capacity of records generates from the my data source which I want to reformat in the same scenario with this post here.

    Additional information about my data source:

    • One data source (file) can output 100+K  data fields
    • The complete data source requires to reformat is a whole month

    0
    Comment actions Permalink
  • Avatar
    Tevita Fainga

    Sample from my data on the screen shot which I marked a single complete record that it also keep repeats on the same format. I want to format the data and using the Key (data) field to become the Name and the rest of the data from Value field goes under. Here the graph and single data source

     

    Attached files

    adsl - 5 Mar 2021.lna
    detail-20200901-00

     

    0
    Comment actions Permalink
  • Avatar
    Gerry Mullin

    Tevita, it looks like you have a lot of Superseded nodes in that data flow, it can be beneficial to replace them. Which leads me onto my next suggestion. Have you tried using the Transpose node? It can be extremely fast, much faster than Pivot Data to Names. Unfortunately right now Pivot - Data to Names is using a lot of superseded nodes within it, whereas Transpose is a newly written node. Transpose is excellent for your case of switching the column Key to the field name and populating it with the field Value. I don't think I quite understand what RowId does, I would have presume it's just a row count, in which case you can use execCount after you transpose quite easily.

     

    Look at the data flow attached. I just added onto your work a Transpose node to pivot the data. I gave 2 options for how to deal with the RowId. As I said I would have presumed it's a count per row, your logic right now evaluates it to 0 all the time.

     

    Attached files

    adsl.lna

     

    0
    Comment actions Permalink

Please sign in to leave a comment.