Pivot multiple files on first N columns

[rnadgir001]

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)

[tstack]

rnadgir001:

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)

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]

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]

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

[rnadgir001]

I think i got it , Just reference it as $.entries()