Hi,
I have the following data:
Line | Description | Entity1 | Entity2 | Entity3 | Entity4 |
1 | ABC | 3 | 4 | 0 | 0 |
2 | BCD | 0 | 0 | 0 | 0 |
3 | EFG | 2 | 0 | 0 | 0 |
4 | HIJ | 0 | 0 | 0 | 0 |
5 | KLM | 12 | 0 | 6 | 0 |
6 | LMN | 0 | 0 | 0 | 0 |
7 | MNO | 0 | 19 | 0 | 0 |
I'm trying to use a dynamic select tool and other tool to select only the rows and entity with numbers. I can use a normal filter if it's just 1 entity. However, I'm not sure how to apply that to multiple entities and I don't want to select each Entity column everytime. Because sometimes, there will be a lot of entities.
The final result should look something like this:
Line | Description | Entity1 | Entity2 | Entity3 |
1 | ABC | 3 | 4 | 0 |
3 | EFG | 2 | 0 | 0 |
5 | KLM | 12 | 0 | 6 |
7 | MNO | 0 | 19 | 0 |
Thanks for your help in advance!
Solved! Go to Solution.
I would suggest the following:
1. Transpose the data so it is row based
2. Filter out cells with no data (assumed to be 0 in your example)
3. Create a set of line ids with some data
4. Join this back to original input to make set of rows with some data
5. Using the transposed set create a set of field names with some data
6. Use a dynamic rename to set the description for these fields then use this in a dynamic select to pick columns with data and combine with a chosen 'white-list'