Capture element index within Expand function
I have a json shown below that I am converting to a list and then expanding out so that I get a row for each element within the items json. The order is very important. Does the expand function preserve the order? I also need to identify the index of each element so that I can analyze the order. I can't figure out how to capture the index in which the element corresponds to
items [
{itemid: "1234", desc: "yellow widget", location: "new york"},
{itemid: "5678", desc: "blue widget", location: "chicago"},
{itemid: "4937", desc: "red widget", location: "connecticut"},
]
Result I'm looking for is below. The piece I'm missing is how to capture the index value. The items json can have any number of elements, not always 3 like in this example.
index itemid desc location
0 1234 yellow widget new york
1 5678 blue widget chicago
2 4937 red widget connecticut
Any help is appreciated.
-
Official comment
The order is not guaranteed following an EXPAND function. In general, the rows between sheets (or transformative functions like EXPAND) do not guarantee the same order in as out. This helps us parallelize the processing more when not required.
To accomplish your goal, I would recommend a slight adjustment to your approach to ensure that you accurately capture the index you're expecting for each element. Specifically, here are the steps I would take:
- In "Sheet1" convert the JSON to a LIST object using the JSONTOLIST function.
- In "Sheet1" add a column using the SIZE function for the LIST object you created.
- In "Sheet2" use the EXPAND function to create a numerical list of indices between 0 and SIZE-1 from step 2.
- In "Sheet2" use the LISTELEMENT function to reference the LIST object from step 1 with the index from step 3.
Let us know if this solves your analysis challenges.
Comment actions -
Thanks Joel. I'm getting lost with step 3.
[1] In "Sheet1" convert the JSON to a LIST object using the JSONTOLIST function.
items_list = JSONTOLIST(#items)
[2] In "Sheet1" add a column using the SIZE function for the LIST object you created.
list_size = SIZE(#items_list)
[3] In "Sheet2" use the EXPAND function to create a numerical list of indices between 0 and SIZE-1 from step 2.
ind = EXPAND(<what-is-referenced-here>)
[4] In "Sheet2" use the LISTELEMENT function to reference the LIST object from step 1 with the index from step 3.
item_ord = LISTELEMENT(#items_list, #ind)
Please sign in to leave a comment.
Comments
4 comments