DSXchange

Hi,

I am using infosphere datastage v8.7 on aix.

can someone please suggest me how can I get expected output using datastage.

my input data:

id,id2,location
1, k12, CA, WA, NY
2,K8,VA,TX

expected output:

id,id2,location
1,k12,CA
1,k12,WA
1,k12,NY
2,k8,VA
2,k8,TX

Based on the sample input and output, I believe that requirement is to horizontally pivot data. See Horizontal Pivot Example using Pivot Enterprise Stage.

Your input needs to have sufficient location columns defined to cover all possibilities in the data.

The horizontal pivot stage appears to be reading data from 3 separate column as below

Table 1. Input data for a simple horizontal pivot operation


https://www-01.ibm.com/support/knowledg ... ample.html

However my data is comma separated and its all in 1 column (location).

when I designed the job using pivot enterprise, under pivot properties I defined as location. the output still the same as input.

my input data:

id,id2,location
1, k12, CA, WA, NY
2,K8,VA,TX

expected output:

id,id2,location
1,k12,CA
1,k12,WA
1,k12,NY
2,k8,VA
2,k8,TX

Thanks

Before data is pivoted, use either Column Import stage or Tranformer's FIELD function to derive multiple Location columns ('LOC1', 'LOC2' ...) from 'LOCATION'.

If your input is variable as to the number of columns for location, but the first two identifier columns are static, then just use a transformer.

Separate the first 2 columns using the field function.
Get a count of the remaining delimiters and use the loop functionality to output a row for each delimited value.