Job to move Data in Vertical format to Horizontal Format

[MukundShastri]

Hi Sunanda,

Since all the data required in the target is numeric I have solution for it:
1] Use a transformer to transfer your data to following
Id page1 page2 page3 page 4
101 10 0 0 0
101 0 11 0 0
102 19 0 0 0
102 0 20 0 0
102 0 0 30 0
and
so on..
(you can use stage variable to count input row number for one particular id)
2] User an aggregator after 1] above to do group by on id and use aggregate function 'Sum'
Then your output will be:
Id page1 page2 page3 page 4
101 10 11 0 0
102 19 20 30 0
and
so on..

[ray.wurlod]

0. You also need to set "normalized on Page" when viewing. If you're only seeing one value your update rule may have been set to "insert new rows only" or you may not have normalized on when loading. Remember that the row's identity is based on the key column, which is not multi-valued.

1. All values are stored in the Page column.

2. The delimiter character is a "value mark", which you can access (in the Field function for example) using the system variable @VM.

3. Yes.

[vivekgadwal]

CT_VAL_GEN_KEY CT_ELE_UNQ_KEY CT_ELEMENT_VALUE
1649 216 13
1649 182 45
1649 217 67
1649 218 87
1649 219 123
1649 220 86
1649 1 EXISTING

1650 216 15
1650 182 56
1650 217 77
1650 218 84
1650 219 145
1650 220 89
1650 1 EXISTING

Each CT_VAL_GEN_KEY will have 7 CT_ELE_UNQ_KEY and wil have 7 values for each UNQ Key.

I have to read Gen Key and based on the UNQ KEY I need to populate in the fields of the Out put file as given below


CT_VAL_GEN_KEY CT_CD_FORM CT_CD_TAX CT_DC_FORM_15
CT_ID_MODULE CT_ID_TYPE_WORK CT_ID_TYPE_WORK_1 STATUS
-------------- ---------- --------- ------------- ------------ --------------- ----------------- ------
1649 13 45 67 87 123 86 EXISTING

1650 15 56 77 84 145 89
EXISTING


IS there any way I can do it in DS? I tried to use Pivot stage and I believe it doesn't support this requirement.

Hello,

As mentioned by Vincent McBurney, Pivot stage cannot handle the vertical pivoting. Unless I am mistaken with the requirement given here, this can be done using Hash files.

In one job take the source as your incoming file with CT_VAL_GEN_KEY and the CT_ELEMENT_VALUE fields. The target would be a Hash file (say for our discussion sake, we call it Hash1). There would also be a lookup from the same hash file Hash1. The design is as shown below:

Code: Select all

                          Hash1 Hash file
                                   |
                                   | Ref
                                   |
                                   V
Source file-----In--------->Transformer-----Out-------->Hash1 Hash file

Within the Hash1 file (target) you would check "Create file" so that this file is created everytime the job runs. The lookup Hash file, should not be having the create file option checked. The idea is to read the same Hash file that is getting created in this job.
In the Hash files, make CT_VAL_GEN_KEY as the key column and the ELEMENT field as non-key column. In the transformer, for the ELEMENT field derivation, put the following logic:

Code: Select all

If Ref.NOTFOUND then TRIM(in.DESC) else TRIM(Ref.DESC):<Delimiter>:(in.DESC)

Delimiter is any delimiter you would like to choose, like '|' or ',' etc. The output of this would look like this (with delimiter '|')

Code: Select all

CT_VAL_GEN_KEY                            CT_ELEMENT_VALUE
1649	                                13|45|67|87|123|86|EXISTING

In other jobs, use 'field' function to choose the values to be populated in a particular column!

As I mentioned before, I might have misunderstood the requirements. So, please let me know if this works for you or if I have mistaken somewhere!