Forum Discussion

rnadgir001's avatar
rnadgir001
New Contributor
8 years ago

Pivot multiple files on first N columns

Pivot snap works well with one file format where i have to know the column i want to pivot on
However, we have a scenario where we want to pivot large number of files of different number of columns but the first first three columns are the same in all of them and those are the ones we want to remain as columns and pivot the rest

The Pivot snap comes short in this scenario and we were wondering if this could be achieved with some other snaps ( short of writing something in Python etc)

sample_pipeline

4 Replies

  • tstack's avatar
    tstack
    Former Employee
    8 years ago

    You can use the expression language to turn the object into an array and then use a JSON Splitter to split the array into separate documents. I’m attaching an example pipeline that does roughly the same thing as described in the Pivot documentation.

    PivotWithMapperAndSplitter_2018_04_25.slp (7.7 KB)

    The pipeline uses the entries() function on objects to get an array of the properties in the object and then converts it into an array of objects with the properties ‘expense’ and ‘amount’.

    • rnadgir001's avatar
      rnadgir001
      New Contributor
      8 years ago

      Thank you so much for your response and the link to the sample pipeline. it seems quite promising . will look into this against our scenario.

      • rnadgir001's avatar
        rnadgir001
        New Contributor
        8 years ago

        in your expression you referenced person

        My json does not have a nice header like yours does
        How do i add a header or otherwise reference my json array
        in
        My JSON -
        {ID:001, GCUSA:001, GEONAME:United States, YCOORD:37.517544, XCOORD:-92.173105, SEGMNTID:01, SEGNAME:The Wealth Market, Base Count:2813090, %Comp:2.27, NDP_014_C:49484, NDP_014_I:45…}
        “ID”: “001”
        your JSON-
        “person”: {Name:Sam, Month:Jan, Transportation:200, House Rent:1500, Internet:100, Food:500}

        Your expression
        $person.entries().filter(x => [‘Name’, ‘Month’].indexOf(x[0]) == -1).map(x => { expense: x[0], amount: x[1] })

        I want something similar
        $??MYJSON??.entries().filter(x=>[‘id’,‘GCUSA’],indexof(x[0] == -1).map(x=>{variable:x[0],values:x[1])
        something like that but i dont know how to refer to my JSON array