scenario

sudhana · Post by **sudhana** » Thu Feb 09, 2012 4:59 am

Hi can anyone plese help me on below

source table name A A B B B C C D In source table data like this but I want traget table like this name count A 1 A 2 B 1 B 2 B 3 C 1 C 2 D 1 pls any one one solve this........

HendrikB · Post by **HendrikB** » Thu Feb 09, 2012 5:23 am

Just to make it more clear for me ...

Source Table contains data like this:
TABLE_NAME
---------------
A
A
B
B
B
...

And you want to achieve this:

TABLE_NAME COUNTER
--------------- -----------
A 1
A 2
B 1
B 2
B 3
...

If this is your requirment I would think about some grouping / transformer (stage variables) logic ...

ray.wurlod · Post by **ray.wurlod** » Thu Feb 09, 2012 7:14 am

Pivot.
Sort (if necessary).
Count.
Pivot.

Terse.
Yes.
Like your subject.

sudhana · Post by **sudhana** » Thu Feb 09, 2012 9:29 am

Hi Ray,

will you please explain clearly....am not getting your point.

ray.wurlod · Post by **ray.wurlod** » Thu Feb 09, 2012 6:29 pm

What's not clear?

RAJARP · Post by **RAJARP** » Thu Feb 09, 2012 10:17 pm

Hi Sudhana,

You can design a job as below

Source--->Sort--->Transformer--->Target

Sort your i/p data based on the only input column you have.

In the following transformer, you can use the three stage variables in the same order as below.

1.Column1Changed =>if link.COLUMN1 = PrevColumn1 then 'NC' else 'C'
2.Counter => If Column1Changed='C' Then 1 else Counter+1
3.PrevColumn1 =>link.COLUMN1

Map the value of stagevariable 'Counter' to your second column.

This should work.

Do any of you see any problem with this approach???

Regards,
Raja R P

sudhana · Post by **sudhana** » Fri Feb 10, 2012 12:26 am

Hi Raja,

its working fine

Thank you somunch for your help..

vasubabu · Post by **vasubabu** » Fri Feb 10, 2012 1:30 am

thanks

ray.wurlod · Post by **ray.wurlod** » Fri Feb 10, 2012 1:53 am

Hijack alert!!!

You're running on two nodes, aren't you? Think about it.

pandeesh · Post by **pandeesh** » Fri Feb 10, 2012 2:00 am

For the input:

Code: Select all

a
a
a
b
b
c
a
b
d
e
b
d
e

i am getting:

Code: Select all

Peek_6,0: id:a Counter:1
Peek_6,0: id:a Counter:2
Peek_6,0: id:a Counter:3
Peek_6,0: id:a Counter:4
Peek_6,0: id:b Counter:1
Peek_6,0: id:b Counter:2
Peek_6,0: id:b Counter:3
Peek_6,0: id:b Counter:4
Peek_6,0: id:c Counter:1
Peek_6,0: id:d Counter:1
Peek_6,0: id:d Counter:2
Peek_6,0: id:e Counter:1
Peek_6,0: id:e Counter:2

Job design is:

Code: Select all

seq..file-->Sort-->Transformer-->peek

Stage variables:

Code: Select all

StageVar: input.id
Counter: if input.id=StageVar1 then Counter+1 else 1
StageVar1: StageVar

Let me know,if you face any issues.

Thanks

HendrikB · Post by **HendrikB** » Fri Feb 10, 2012 2:22 am

Why using so much stages variables?

You could group the data in the sort stage (by TableName) and then use the created key change column in one stage variable in transformer.

The syntax of <stagevar>: If KeyChange
Then 1
Else <stagevar> +1

That's what I meant with grouping / transformer logic in my post ...

gonuguntla · Post by **gonuguntla** » Fri Feb 10, 2012 11:26 am

what is the stage variable logic here?

how variables required ?

pandeesh · Post by **pandeesh** » Fri Feb 10, 2012 11:32 am

gonuguntla wrote:what is the stage variable logic here?

Dear mr.Gonuguntla,

can you please start your own topic for your queries?

Thanks

RAJARP · Post by **RAJARP** » Fri Feb 10, 2012 3:09 pm

HendrikB

You could group the data in the sort stage (by TableName) and then use the created key change column in one stage variable in transformer.

The syntax of <stagevar>: If KeyChange
Then 1
Else <stagevar> +1

This should work like charm

I didn't think of 'create key change' column and this is less messy as well

Regards,
Raja R P