combine two columns in one single dimension

[jduluc12]

Hi,

I have a data set that looks like this

col1     col2        amount

A          B            100

C          D            200

i want to build a report which looks like this

col1+ col2        Amount  

A                    100

B                    100

C                    200

D                    200

Any suggestion?

[vishsaggi]

Try this?

LOAD col1 AS NewCol, amount INLINE [

col1,     col2,        amount

A ,         B,            100

C,          D,            200

];

Concatenate

LOAD col2 AS NewCol, amount INLINE [

col1,     col2,        amount

A ,         B,            100

C,          D,            200

];

[jduluc12]

Hey Vishwa

I do not have access to load script.I need to do it using expression.

[vishsaggi]

I am not sure if that is possble, but what is your actual data looks like? can you share some of your actual data?

[MarcoWedel]

Hi,

although it would be a way easier solution if you could create a combined field in the script, a front end only solution might be:

=ValueList($(=Concat(Distinct Chr(39)&col1&Chr(39),',')),$(=Concat(Distinct Chr(39)&col2&Chr(39),',')))

Sum(If(ValueList($(=Concat(Distinct Chr(39)&col1&Chr(39),',')),$(=Concat(Distinct Chr(39)&col2&Chr(39),',')))=col1,amount))+Sum(If(ValueList($(=Concat(Distinct Chr(39)&col1&Chr(39),',')),$(=Concat(Distinct Chr(39)&col2&Chr(39),',')))=col2,amount))

hope this helps

regards

Marco