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
Does anyone know how to set this up?
Thanks in advance,
Mikis
Solved! Go to Solution.
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....
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?
Would this not work for you?
Hello @LordNeilLord
Apologies for not giving the full problem from start
That wouldn't work, because one respondent can give answers for more than one company and/or brand
in the below example, if we filter company & brand is not null, then we get 3 combinations: A/x, A/y and B/y
That means that we need to duplicate the score that respondent 1 gave on company A for brand x and y.
input:
Output with your flow (so A/x is gone):
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: