Hi Anand,
Looks like you need to split your crosstable function, something along the lines of (also use the Header offset in the Data load UI to get rid of the first row)...
Table1:
LOAD
B
From PivotTable.xls;
RepairCase_Temp:
crosstable(WeekendRepairCase, Data, 1)
Load
Market,
@2 as 19-Feb,
@3 as 26-Feb,
@4 as 03-Mar,
@5 as 12-Mar,
@6 as 19-Mar
From PivotTable.xls;
left join(Table1)
LOAD
Resident RepairCase_Temp;
AvgSubs_Temp:
crosstable(WeekendAvgSubs, Data, 1)
Load
Market,
@2 as 19-Feb,
@3 as 26-Feb,
@4 as 03-Mar,
@5 as 12-Mar,
@6 as 19-Mar
From PivotTable.xls;
left join(Table1)
LOAD
Resident AvgSubs_Temp;
The problem is your columns names are the same, that's why you need to use the @ to reference the column number. You can use the crosstable function multiple times, with a left join. All you need to do is load straight columns in one load statment, use crosstable function on one part of the source file, left join to the previous table, use crosstable on next part, left join, so on and so forth.
If you keep adding columns as the months go by, you will need a more "intelligent" (automated) piece of script. Can't help you on that, it would take more time than I want to spend answer this thread.
Parameters for crosstable function are as follows:
crosstable(X, Y, Z), where X is the fieldname you want to give to the column headers you are unpivoting, Y is the fieldname you want to give of the data part of the pivoted part of the table, and Z is the number of qualified fields (i.e. the number of columns you are loading in that statement which are already in column format, i.e. typically the row header already contains the fieldname, not a field value).
Hope this helps.
