Skip to main content
Announcements
Discover what’s possible with embedded analytics! March 6, 10 AM ET SIGN UP!
cancel
Showing results for 
Search instead for 
Did you mean: 
EDehzn
Contributor III
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?

1 Solution

Accepted Solutions
edwin
Master
Master

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) 

View solution in original post

2 Replies
edwin
Master
Master

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;

commQV32.png