Value Concatenation

[pravin1581]

Hi All,

There is a requirement . Suppose a file contains the following data :-

Code: Select all

           C1         C2

           A            1
           A            5
           A            10
           A            5
           B            2
           B            1
           C            51

The output file should have the following data :-

Code: Select all

      C1                 C2

       A                 1,5,10
       B                 1,2
       C                  51


  

The output file should contain the concatenated non duplicate values.Please help me out.


Thanx in advance.

[JoshGeorge]

In the input sequential file stage filter option use (Hopefully you have MKS Toolkit or similar installed)

Code: Select all

sort -u

and then use pivot stage to get your results.

[Akumar1]

since it is windows environment,i don't think that ((sort -u))
command can be used.because the option in enabled only in unix environment.

Regards,
Akumar1

In the input sequential file stage filter option use (Hopefully you have MKS Toolkit or similar installed)

Code: Select all

sort -u

and then use pivot stage to get your results.

[JoshGeorge]

Pls. search the forum with key words 'MKS Toolkit' for clarity.


Replied inline

(Hopefully you have MKS Toolkit or similar installed)

[ray.wurlod]

Search the forum for vertical pivot - there are at least three ways to solve this in server jobs, depending on whether the number of rows per key is small or large, fixed or arbitrary. You example suggests small and arbitrary, but who knows if you've supplied the correct data?

[sachin1]

if your input file is a hash file with C1 and C2 as key then i think you get unique records like

C1 C2

A 1
A 5
A 10
B 2
B 1
C 51
Now follow below steps.
your job design will be like

H/C file(hash input file) ----> transformer---> hash(H/C) file(output)


1. Create 2 stage variables like APPEND_C2_VALUE and C1_VALUE.

2.In transformer give the value for C1_VALUE = trim(input_link.C1).

3. In transformer give the value for APPEND_C2_VALUE like below

if input_link.C1 = C1_VALUE
Then APPEND_C2_VALUE :',': input_link.C2
Else input_link.C2

4.in your final output hash file put two columns like A and B.

values for column A = input_link.C1 and value for column B = APPEND_C2_VALUE.

this works fine please check.