DSXchange

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

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,

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'.