Page 1 of 1
vertial & horizontal pivoting with variable number of co
Posted: Fri Feb 20, 2009 3:17 am
by ashish.singh
Need help to do vertical and horizontal pivoting at same time and problem is that FACT columns may vary every time.
Input file -
MRKT|PROD|PRD|FACT1|FACT2|FACT3|FACT4
M1|P1|PR1|F11|F12|F13|F14
M1|P1|PR2|F21|F22|F23|F24
M1|P1|PR3|F31|F32|F33|F34
M2|P2|PR1|F41|F42|F43|F44
M2|P2|PR2|F51|F53|F54|F55
M2|P2|PR3|F61|F64|F65|F66
out put file -
MRKT|PROD|FACT|PR1|PR2|PR3
M1|P1|FACT1|F11|F21|F31
M1|P1|FACT2|F12|F22|F32
M1|P1|FACT3|F13|F23|F33
M1|P1|FACT4|F14|F24|F34
M2|P2|FACT1|F41|F51|F61
M2|P2|FACT2|F42|F52|F62
M2|P2|FACT3|F43|F53|F63
M2|P2|FACT4|F44|F54|F64
Please advice how to chieve this in datastage
Posted: Fri Feb 20, 2009 3:31 am
by Kirtikumar
Ashis,
For one type of pivoting there is stage provided by DataStage as pivot. Try that. For the other you have to use transformer and then aggregation.
If you search you will get many post which discuss this.
Posted: Fri Feb 20, 2009 4:23 am
by Sainath.Srinivasan
Need more information.
Why is the input to appearing in the fashion? That too when it is almost ready to be loaded into fact table !!
Posted: Fri Feb 20, 2009 6:13 am
by ashish.singh
Yes this can be achieved but that is feasible when there will be fixed number of columns . In this case we have variable number of columns.
Posted: Mon Feb 23, 2009 6:50 am
by ashish.singh
We need to develp a job so that we can create two different type of reports in xl file. For one key will be Market,Product ,Period and values will be fact and for one key will be Market,Product and fact and value will be a period.
But we have variable number of columns for period and fact . Means data may be like this as well.
MRKT|PROD|PRD|FACT1|FACT2|FACT3|FACT4
M1|P1|PR1|F11|F12|F13|F14
M1|P1|PR2|F21|F22|F23|F24
M2|P2|PR1|F41|F42|F43|F44
M2|P2|PR2|F51|F53|F54|F55
M2|P2|PR3|F61|F64|F65|F66
M3|P3|PR3|F31|F32||
Posted: Mon Feb 23, 2009 7:03 am
by Sainath.Srinivasan
If I understand correctly, are you saying that there can be variable number of columns ??
In that case, how do you intend to store in a db? The structure will be variable.
Also, you may be better-off using features in xls to change (pivot) the result.
Posted: Mon Feb 23, 2009 7:03 am
by Sainath.Srinivasan
If I understand correctly, are you saying that there can be variable number of columns ??
In that case, how do you intend to store in a db? The structure will be variable.
Also, you may be better-off using features in xls to change (pivot) the result.