Divide Odata JSON parse response into chunks

fardeen9983

I have an Odata endpoint that I query to fetch data. It returns an array of records. Each record in turn has a few key value pairs as well as an array that has varying no of elements per record

{
    .....
    "value" : [
        { "year" : 2019,
          "tax_slot":[
             { "a" : 34, "b" : 45 } //1st record
             ....
             { "a" : 34, "b" : 45 } //nth record
          ]
        }, // 1st record
        ........ 
        { "year" : 2014,
          "tax_slot":[
             { "a" : 34, "b" : 45 } //1st record
             ....
             { "a" : 34, "b" : 45 } //mth record
          ]
        }, // some record
    ]
}

The schema remains the same. The only thing changing across the Value records is the number of elements in the "tax_slot" array.

When I Json Parse it I get something like this

year	2019
tax_slot_0_a	35
tax_slot_0_b	45
....	....
tax_slot_n_a	35
tax_slot_n_b	45
year	2014
.....	....
tax_slot_m_a	35
tax_slot_m_b	45

Now comin to my requirement I need to generate a record ID and assign it to fields of each record

so assign 0 to year - tax_slot_n_b and 1 to the next year - tax_slot_m_b

How do i achieve this as the tax_slot count varies across all records. The expected end output should be like this

year	2019	0
tax_slot_0_a	35	0
tax_slot_0_b	45	0
....	....	0
tax_slot_n_a	35	0
tax_slot_n_b	45	0
year	2014	1
.....	....	1
tax_slot_m_a	35	1
tax_slot_m_b	45	1

Thank you for your time and suggestion. Have a nice day

BenMoss

Hi!

You could do this ID using the multi-row formula tool, checking if the active row = year, if so take the value from the line above and add 1 (thus incrimenting the ID), otherwise keep the same as the line above (thus keeping the same and only changing when it's a new year).

Something like...

IF [fielda] = "year" THEN [ROW-1:ID]+1 ELSE [ROW-1:ID] ENDIF

In your multirow formula tool you would be creating a new field titled ID.

Hope this helps.

Ben