How to achieve other than Pivot

pradeep_nov18 · Post by **pradeep_nov18** » Tue Mar 31, 2009 12:47 am

Hi Experts,

I have requirement

Input:

a b c
1 10 20
2 30 40

Output Required:

a Computed_col(E)
1 10
1 20
2 30
2 40

How to achieve these other than pivot stage.

Can anyone help me on this?

Thanks in advance for your reply! [/b]

mahadev.v · Post by **mahadev.v** » Tue Mar 31, 2009 12:51 am

Any specific reason why you don't want to use the Pivot stage? A custom Build Op stage can be used, but involves a lot of coding.

pradeep_nov18 · Post by **pradeep_nov18** » Tue Mar 31, 2009 12:56 am

mahadev.v wrote:Any specific reason why you don't want to use the Pivot stage? A custom Build Op stage can be used, but involves a lot of coding.

Hi Mahadev,

No reason behind that

Whats that Custom Op?

Any Idea how we can perform in Transformer.

Thanks for the reply

BugFree · Post by **BugFree** » Tue Mar 31, 2009 1:09 am

pradeep,

I guess it can be possible through funnel stage.
From transformer/copy get two links.
Propogate the fields "a" and "b" in 1st link.
Propogate the fields "a" and "c" in 2nd link.
Then combine the records through funnel stage. But make sure that you rename one of the field name as "b" or "c" (otherwise funnel stage will not work properly as it expects same metadata from both the links. ).

pradeep_nov18 · Post by **pradeep_nov18** » Tue Mar 31, 2009 2:59 am

hi Bugfree,

Ya you are correct,we can achieve that

one doubt

suppose my input

a b c d e
1 10 20 30 40 so on.....

how to achieve
a Computed(col)
1 10
1 20
1 30
1 40
So on...

other than pivot stage.

really thanks for sharing your idea,

Pagadrai · Post by **Pagadrai** » Tue Mar 31, 2009 3:04 am

Hi Pradeep,
What I understand is that your number of 'computed columns' in input is not limited.
is this correct ?

pradeep_nov18 · Post by **pradeep_nov18** » Tue Mar 31, 2009 3:05 am

Pagadrai wrote:Hi Pradeep,
What I understand is that your number of 'computed columns' in input is not limited.
is this correct ?

Hi Pagadrai,

Exactly

Pagadrai · Post by **Pagadrai** » Tue Mar 31, 2009 3:17 am

Hi Pradeep,
I can think of a way to do this - though it is not the exact pivoting method.
You dint mention what is the delimiter here.

From your input read the entire record as one column.
say your first record is 1 10 20 30 40.
( i assume delimiter is space)
In the transformer stage, you can get the first value 1 and then replace each of the delimiter ' ' with ' 1,'
so your string looks like 1 1,10 1,20 1,30 1,40
Now, you can replace the space with newline char and write the record 1,10 1,20 1,30 1,40 to output file.

* If you want to further process the resulted records, you have to do it in another job. ( where record delimiter is newline and column delimiter is comma)

pradeep_nov18 · Post by **pradeep_nov18** » Tue Mar 31, 2009 3:48 am

Thanks Much Pagadrai

Let me such and get back to you.

v2kmadhav · Post by **v2kmadhav** » Tue Mar 31, 2009 3:50 am

Pradeep

You never mentioned about it being a file or perhaps i missed it, if you were expecting to read them as column b, c, d, e ...so on.... with 'a' being your key column

i mean would it be like

a, b, c, null, null, null, null
a, b, c, d, e, null, null
a, b, c, null, null, null, null
a, b, c, d, e, f, g, h

Because i guess you would need to specify a metadata on your source stage??

if not would you be reading them as a, concatenated values b??

that case... you can detemine the number of times a delimiter occurs between these values in column 'b' and feed that into a stage variable. and loop it that number of times using a combination of stage variables based on that against your key column 'a'.

chulett · Post by **chulett** » Tue Mar 31, 2009 7:00 am

The technique that Pagadrai mentioned is discussed in this FAQ post by the way.