Exclude fields from dynamic deselect

Hello everyone,

I have a table with key fields and values. The values are only filled in if they're relevant.

In attachment I made an example, the data looks somewhat like this:

fieldA	fieldB	fieldC	fieldD	value1	value2	value3	value4	value5	value6
1	a	x		0,15		0,53		0,09
1	a	y		0,25		0,78		0,11
1	b	x		0,17		0,66		0,49
1	b	y		0,63		0,31		0,36
2	a	x			0,15		0,34		0,89
2	a	y			0,65		0,81		0,29
2	b	x			0,45		0,92		0,19
2	b	y			0,25		0,79		0,28

The flow that I've built filters on fieldA = 1 and deletes the null fields, then filters fieldA = 2 and deletes the null fields.

Both flows are then rejoined ignoring fieldA.

So this should be the result:

fieldB	fieldC	fieldD	value1	value2	value3	value4	value5	value6
a	x		0,15	0,15	0,53	0,34	0,09	0,89
a	y		0,25	0,65	0,78	0,81	0,11	0,29
b	x		0,17	0,45	0,66	0,92	0,49	0,19
b	y		0,63	0,25	0,31	0,79	0,36	0,28

The problem is that fieldD is also blank, so it gets removed and the macro that I've built tries to join on fieldD.

Basically I want to add a statement in my "remove null fields" that it should not get removed

don't remove.PNG

Does anyone know how to set this up?

Thanks in advance,

Mikis

normalize_fields.yxzp

Transformation

Accepted answers

danrh

I have a suspicion that @LordNeilLord is on to something, but if it can't be done that way then an easy option might be to just add back the fields you need. Since they're null anyway, you don't need the data and could just add the columns after your Dynamic Select. Something like this would do the trick:

solution.yxmd

All comments

LordNeilLord

Hey @Mikis

I think there might be an easier way to achieve the output you are looking for.....If you make use of the summarize tool you can use the MAX, min or concat function to combine all of the records together....

Mikis

Hello @LordNeilLord

You're right, but I'm afraid I haven't properly explained my usecase

The data that I have is actually a number of respondents (people) that give scores on companies, brands and the combination of company/brands

Sadly, those 3 types of scores (brand, company & the combination of both) are on individual rows and I want to merge them.

In reality I filter on company & brand is not null, and then I use a join to add the brand & company score.

In the below example, 'age' is not filled in, but I do want to join on age IF it would be filled in. So basically it should not be removed.

respondent	age	gender	company	brand	brand_score	company_score	company_brand_score
1		male	A			5
1		male		x	3
1		male	A	x			2
2		female	A		6
2		female		x		8
2		female	A	x			9

And this table should become

respondent	age	gender	company	brand	brand_score	company_score	company_brand_score
1		male	A	x	3	5	2
2		female	A	x	6	8	9

Does this make more sense?

danrh

solution.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703