The data in (2D format) Excel sheet is to arrange in table

[DataStageCnu]

hi all,

In excel sheet my source data is in the following format

--------------A--------B-----C-----D----
========================
|Headers----------> Cust1 Cust2 Cust3
|
|Row 1--->month01 01d1 01d2 01d3
|Row 2--->month02 02d1 NULL 02d3
|Row 3--->month03 03d1 03d2 NULL
|Row 4--->month04 04d1 04d2 04d3
|Row 5--->month05 05d1 05d2 05d3
========================
The header of Months col is empty. It is like a 2D matrix.

Now i need to process and arrange the data in follwing format in seq file
======================
|Header-->Months--Dealers--Sales
|
|Row 1--->month01 dealer1 01d1
|Row 2--->month01 dealer2 01d2
|Row 3--->month01 dealer3 01d3
|Row 4--->month02 dealer1 02d1
|Row 5--->month02 dealer2 NULL
|Row 6--->month02 dealer3 02d3
|Row 7--->month03 dealer1 03d1
|Row 8--->month03 dealer2 03d2
|Row 9--->month03 dealer3 NULL
|and so on.....
======================

By using pivot i can able to get data in following order

Row 1--->month01 01d1
Row 2--->month01 01d2
Row 3--->month01 01d3
Row 4--->month02 02d1
Row 5--->month02 NULL
Row 6--->month02 02d3
Row 7--->month03 03d1
Row 8--->month03 03d2
Row 9--->month03 NULL
and so on.....

Can u give some suggestion how to get corresponding header value to respective row.

Thanks
cnu

[roy]

Hi,
If you only need to add the header you can check the First line is columns names option in your sequential file stage and have a table definition with the desired column names.
If you want to add the Dealer column use a Transformer.
I Hope I understood you correctly.
By the way you can alway write a DS Routine to format the input row in case there is no stage oriented solution in the version you use.

IHTH,

[KeithM]

You can create your 'Dealer' column after the pivot using a stage variable in a transformer stage. Name the stage variable like SV1 and initialize it to 0. Then the logic would be something like 'If SV1 = 3 then 1 else SV1 + 1.' This will give you the 1,2,3 for each group and you can just cancatenate this to a constant 'Dealer'.