Capture element index within Expand function

2 followers
0
Avatar

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.

Dan

Official comment

  • Avatar
    Joel Stewart

    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.

    0

4 comments

  • Avatar
    Dan

    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
  • Avatar
    Joel Stewart

    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
Please sign in to leave a comment.