Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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)
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:
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;