Create new row for each location

[pavan_test]

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

[rkashyap]

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

[ray.wurlod]

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

[pavan_test]

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

[rkashyap]

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

[ShaneMuir]

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.