Announcements
cancel
Showing results for
Did you mean:
Contributor III

rows into column

Hi All,

I have been trying to transform rows of my data into a new column to mimick SQL outer join. Example as below:

current data:

 Customer MIA Value item amount A 1 200 12 B 1 325 10 C 1 450 5 A 2 12 2 C 2 700 32

desired outcome:

 Customer MIA Value item amount MIA Value item amount A 1 200 12 2 12 2 B 1 325 10 C 1 450 5 2 700 32

I am currently preferred to have the data as granular as possible, thus i am wondering if this transformation is possible to be executed using straight table expressions?

Labels (4)

• Scripting

1 Solution

Accepted Solutions
Master II

two possible options:

1. use a pivot table and use MIA as the pivoted dimension - simplest i think

2. continue using a straight table:
- column 2create measure use =only({<MIA={1}>}MIA) or just =1
- columns 3 and 4 add in your set analysis {<MIA={1}>}, this will compute only for MIA =1
- column 5 create measure use =only({<MIA={2}>}MIA) or just =2
- columns 6 and 7 add in your set analysis {<MIA={2}>}, this will compute only for MIA =2

so for column 3, if your original measure is
=sum(Value)
it becomes
=sum({<MIA={1}>}Value)

2 Replies
Master II

two possible options:

1. use a pivot table and use MIA as the pivoted dimension - simplest i think

2. continue using a straight table:
- column 2create measure use =only({<MIA={1}>}MIA) or just =1
- columns 3 and 4 add in your set analysis {<MIA={1}>}, this will compute only for MIA =1
- column 5 create measure use =only({<MIA={2}>}MIA) or just =2
- columns 6 and 7 add in your set analysis {<MIA={2}>}, this will compute only for MIA =2

so for column 3, if your original measure is
=sum(Value)
it becomes
=sum({<MIA={1}>}Value)

Try this,

``````tab1:
Customer, MIA, Value, item amount
A, 1, 200, 12
B, 1, 325, 10
C, 1, 450, 5
A, 2, 12, 2
C, 2, 700, 32
];

tab2:
Generic