Data360 Analyze

 View Only
  • 1.  Transpose columns to rows

    Posted 03-03-2021 07:51

    Hello

    I would like to transpose values from single column into single row and create as much columns as needed for each value stored in source column.

    Input data

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

    Result should be

    name type1 type2 type3 type4
    bob a b c  
    mary x y    
    john x y z f

     

    I don't know how many types there will be, so i need the Data360 to create as much columns as needed.

    Thanks a lot.



  • 2.  RE: Transpose columns to rows

    Posted 03-03-2021 07:55

    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



  • 3.  RE: Transpose columns to rows

    Employee
    Posted 03-03-2021 08:37

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

    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

     



  • 4.  RE: Transpose columns to rows

    Posted 03-03-2021 08:54

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



  • 5.  RE: Transpose columns to rows

    Posted 03-03-2021 08:56

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



  • 6.  RE: Transpose columns to rows

    Posted 03-03-2021 15:45

    It worked! Thanks a lot!



  • 7.  RE: Transpose columns to rows

    Posted 03-04-2021 13:24

    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



  • 8.  RE: Transpose columns to rows

    Posted 03-04-2021 13:52

    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

     



  • 9.  RE: Transpose columns to rows

    Employee
    Posted 03-04-2021 17:49

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

    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