Capture element index within Expand function

Comments

4 comments

  • Official comment
    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.

    Comment actions Permalink
  • 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
    Comment actions Permalink
  • 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
    Comment actions Permalink
  • Dan

    Thanks, this is exactly what I was looking for.

    1
    Comment actions Permalink

Please sign in to leave a comment.