Aggregating fields of the same name in calculated dimension

[JonasM95]

Hello,

I have data that looks like this.

order nr	food from data source 1	food from data source 2	quantity from data source 1	quantity from data source 2
1	burger		1
2	salad		2
3	pizza		3
4	others		3
5		salad		1
6		burger		4
7		others		2

For each category I need a row in a table chart. How do I need to formulate my dimension and expressions in order to achieve the following result:

food type (dimension)	quantity combined
burger	5
salad	3
pizza	3
others	5

Fields:

• source1.food
• source2.food
• source1.quantity
• source2.quantity

Thank you for your help!

Best regards

Jonas

[shiveshsingh]

Hello

Try this in script.

T:LOAD * INLINE [

order nr, food from data source 1, food from data source 2, quantity from data source 1, quantity from data source 2
1, burger, , 1,
2, salad, , 2,
3, pizza, , 3,
4, others, , 3,
5, , salad, , 1
6, , burger, , 4
7, , others, , 2
];

F:
load [food from data source 1] as Food_Type, [quantity from data source 1] as Quantity
Resident T;

F:
load [food from data source 2] as Food_Type, [quantity from data source 2] as Quantity
Resident T;

drop table T;

Dimension - Food_Type

Expression - Sum(Quantity)

[pradosh_thakur]

Hi 

I think you need to change the way the data is loaded. You have food and quantity from two data sources and essentially they will be concatenated. You can definaitly load it and do the transformation but why not load it properly

Let's say you ddata from datasource 1 looks like this 

orderno  food  quantity

from data source 2 it looka like 

orderno  food1 quantity1

you just change it to something like below

load orderno,  food , quantity

from data source 1;

load orderno,  food1 as food , quantity1 as quantity

from data source 2;

Let me know if you didn't understand.

[sunny_talwar]

If you don't want to change your data model, you can try this with an island table approach

Table:
LOAD [order nr],
	 If(Len(Trim([food from data source 1])) > 0, [food from data source 1]) as [food from data source 1],
	 If(Len(Trim([food from data source 2])) > 0, [food from data source 2]) as [food from data source 2],
	 If(Len(Trim([quantity from data source 1])) > 0, [quantity from data source 1]) as [quantity from data source 1],
	 If(Len(Trim([quantity from data source 2])) > 0, [quantity from data source 2]) as [quantity from data source 2];
LOAD * INLINE [
    order nr, food from data source 1, food from data source 2, quantity from data source 1, quantity from data source 2
    1, burger,  , 1,  
    2, salad,  , 2,  
    3, pizza,  , 3,  
    4, others,  , 3,  
    5,  , salad,  , 1
    6,  , burger,  , 4
    7,  , others,  , 2
];

Dim:
LOAD * INLINE [
    Dim
    1
    2
];

Dimension

=Pick(Dim, [food from data source 1], [food from data source 2])

Expression

Sum([quantity from data source 1]) + Sum([quantity from data source 2])