Hi there,
in my data set I have the following format:
I want to put all the unique bracket values from the three columns in one column and then fill the total amounts for benefit, and if no there a zero will be printed, so instead of having many columns I will have more rows. Thanks in advance
the desired output is as follow:
Hi @MohHabib , it is likely that part of the solution here will involve pivot/unpivot but I’d doubt that will be the whole story,
and there may be alternative options.
If you can upload your sample data from the screenshot I think you are more likely to find somebody who can assist, as typing it in will take too long and it is likely to involve a little trying out of ideas. Thanks.
Hi @MohHabib , thank you for uploading the sample data. I think there is some aggregation that is apparent in your “required output” that you didn’t mention in the text. What are the required groupings for the aggregations of a given benefit?
e.g. I’m guessing that the first line of your output Benefit 1 (18) is made up from the two entries for 02, [0-20] with values 7 and 11. So are we to assume that such aggregations grouped by company, department, manager/non and team.
And what happens if the same benefit bracket appears with a value in different benefit value columns?
Are they then aggregated, and if so into which column (1 to 3) does the final value appear?
Hi @takbb
Yes you are right the aggregation is grouped by company, department, manager/non and team all together.
for your query, if the same bracket exist in both benefit it will be added to the benefit column
For example: if you have [0,20] for benefit_1 is 15 and for benefit_2 is 26 and not exist for benefit_3
the output aggregated on the groups mentioned above should make the following output
Bracket Benefit_1 Benefit_2 Benefit_3
[0,20] 15 26 0
Hi @MohHabib , thank you for the clarification.
The attached workflow gives one possible approach. As it turned out I didn’t use pivot/unpivot but instead pulled out the required groups of columns, performed the aggregation on the amounts and then joined reassembled each group of rows using concatenation. I think it gives the result, although I couldn’t determine if you had a particular sort order for the final result. Hopefully it gives something to work with.
merge tabulated columns and aggregate.knwf (45.1 KB)
My solution is slightly more “manual” than I would like. I prefer to do something a little more generic (e.g. if you had an extra bracket amount, then you’d have to manually add another branch), Possibly we could add a loop instead (maybe somebody will!) but hopefully this will be good enough. There may be better ways that somebody can think of!
and Welcome to the KNIME community! 
