Capture element index within Expand function

Follow

Dan

• June 22, 2017 16:56

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.

0

• Official comment

  

  Joel Stewart

  • June 22, 2017 20:56

  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: 

  1. In "Sheet1" convert the JSON to a LIST object using the JSONTOLIST function. 
  2. In "Sheet1" add a column using the SIZE function for the LIST object you created. 
  3. In "Sheet2" use the EXPAND function to create a numerical list of indices between 0 and SIZE-1 from step 2. 
  4. 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 Permalink
• 

  Dan

  • June 22, 2017 22:47

  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)

  0

  Comment actions Permalink
• 

  Joel Stewart

  • June 22, 2017 22:54

  In step 3, the expand function should look something like the following: 

  EXPAND(RANGE(0;#Sheet1!SizeColumn-1))

  Specifically, we are referencing a hard-coded value of 0 and a reference to the SizeColumn from Sheet1 (minus 1 to accommodate the 0-based indexing).

  0

  Comment actions Permalink
• 

  Dan

  • June 23, 2017 02:07

  Thanks, this is exactly what I was looking for.

  1

  Comment actions Permalink

Please sign in to leave a comment.