rows into column

[EDehzn]

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?

[edwin · Accepted solution]

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) 

[Saravanan_Desingh]

Try this,

tab1:
LOAD * INLINE [
    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
LOAD Customer, 'MIA'&MIA, MIA
Resident tab1;

Generic
LOAD Customer, 'Value'&MIA, Value
Resident tab1;

Generic
LOAD Customer, 'item amount'&MIA, [item amount]
Resident tab1;

Drop Table tab1;