Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Divide Odata JSON parse response into chunks

fardeen9983
8 - Asteroid

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

year2019
tax_slot_0_a35
tax_slot_0_b45
........
tax_slot_n_a35
tax_slot_n_b45
year2014
.........
tax_slot_m_a35
tax_slot_m_b45

 

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

year20190
tax_slot_0_a350
tax_slot_0_b450
........0
tax_slot_n_a350
tax_slot_n_b450
year20141
.........1
tax_slot_m_a351
tax_slot_m_b451

 

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

2 REPLIES 2
BenMoss
ACE Emeritus
ACE Emeritus

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 

fardeen9983
8 - Asteroid

I do not have an ID field in the Data and dont see it as an inherent property in the Multi-Row Formula. Do I need to add that? 

And for the first record where [Field] = "year" what will it fetch for [ROW-1:ID]?

Labels