asvictor wrote:
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!