Merging 2 Fields in a single field and show each field along with the Merged fields

[chanin7893]

Hello Everyone,

Suppose i have a Table with the Following Values 

Item	Cost
Apple	10
Orange	15
Lemon	25
Mango	35
Strawberry	20

I want to add Orange and Lemon as Citrus, but i also want the values of Orange and Lemon to be present in the table, so my Expected output is,

Item	Cost
Apple	10
Orange	15
Lemon	25
Mango	35
Strawberry	20
Citrus	40

However, I'm not able to get to the desired output (the are other dimensions and Measures in the data and the respective values of Lemon and Orange should be added for Citrus (i.e. 15 + 25 = 40) as well)

[sunny_talwar]

And you are looking to do this in the script or front end itself?

[PrashantSangle]

Try below
Table1:
Load *
From TableName;
Concatenate
Load 'Citrus' as Item,
Sum(Cost) as Cost
Resident Table1
Where wildmatch(Item,'Orange','Lemon');

[jheasley]

So there are 2 approaches i can think of that will get you there:

Approach 1 - make a category field as another dimension, ie "Product Family" then use that in your view.  it would seem that having a line item for Citrus as well as a line item for each of its individual components would lead to overstating value.

Approach 2 - concatenate a second grouped table to your first

Products:
Load * Inline [
Item,    Cost
Apple,   10
Orange, 15
Lemon,  25
Mango,  35
Strawberry,  20
];
concatenate
load
'Citrus' as Item,
sum(Cost) as Cost
Resident Products
Where Match(Item,'Orange','Lemon')
Group by Item;

[PrashantSangle]

@jheasley , You don't need to use group by clause here.

[chanin7893]

Anywhere would be fine, but nothing like it if it can happen in the front end

[sunny_talwar]

You have already received some script based solution from other experts. Have you tried those?