How to Filter certain entity and show only the rows with numbers?

calvinwong

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!

jdunkerley79

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'

Dynamic Table.yxmd