You can translate the question and the replies:

Flatten array with column of index of value in array

Hi, I have a view in which there is an ID column, and a column with an array in that I want to flatten. This of course flattens the array so each value in the array becomes a new row, with the same ID column across multiple rows. What I want to be able to do is add another column that includes the index of the flattened value in respect to the array it came from. eg: starting view: ID | array 001 | {'A','B','C'} 002 | {'D','E','F'} Desired view: ID | arrayvalue | arrayindex 001 | A | 1 001 | B | 2 001 | C | 3 002 | D | 1 002 | E | 2 002 | F | 3 Many thanks in advance for any suggestions.
user
03-11-2020 04:50:03 -0500

1 Answer

Hi, We would like to let you know that it is possible to achieve the desired view with an unpivot operation on top of your flattened view and by including an additional column in the output for the index. To explain further, as in your example, you would need to do the following: * Flatten your initial view in order to have a view with 4 fields: ID, value0, value1, value2 (where value0, value1 and value2 represent the columns of the three array fields) * The idea is to have three different views for each value field, so that you can add manually an index to each of these "branches". * Create for each of the value fields (value0, value1, value2) an own projection view by taking into account the following * Rename the respective value field (value0, value1, value2) to a common name, such as value and remove the other two value fields. * Add for each of these projection views a new column in the output for the index of the value (array). You could call it for example index and provide as expressions "1", "2" and "3" depending on the "branch" that you are editing. * Once you have created these three projection fields with each of them having a common name for the value and an index column, you could simply create a union view and selecting all of these three views as branches for the union. * The result should be now as the desired view in your example. Hope this helps!
Denodo Team
27-11-2020 13:33:46 -0500
You must sign in to add an answer. If you do not have an account, you can register here