Hi ashishkumar
There are two ways, but will involve restructuring the script:
1) Make your main data table more "vertical"
By this I mean writing the script so that the StDate and EndDate are now in one column. You could write this easily in the script using a crosstable statement:
Data:
noconcatenate load Employee, [St Date] as Date,[St Amount] as Amount, 'Start' as Type from initaltab;
concatenate(Data) load Employee, [End Date] as Date,[End Amount] as Amount, 'End' as Type from initialtab;
This will result in the following table:
| Employee | Type | Date | Amount |
|---|
| A | Start | 2011Q1 | 100 |
| A | Start | 2011Q2 | 200 |
| A | End | 2012Q2 | 550 |
| B | Start | 2011Q1 | 300 |
| B | End | 2012Q1 | 550 |
This is now very easy to pivot into the table you require.
2) The other alternative would be to create a stand-alone table of dates, eg using inline to create a field called [period] and the values 2011Q1, 2011Q2...etc
You could then use this as the dimension to base your new table on, and use if statments to match the values from the other table in it in expressions.
EG
expression for St Dt Amount would be
= sum(if([St Date] = [period],[St Date Amount]))
Hope this helps,
Regards,
Erica
