Page 1 of 1
scenario
Posted: Thu Feb 09, 2012 4:59 am
by sudhana
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........
Posted: Thu Feb 09, 2012 5:23 am
by HendrikB
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 ...
Posted: Thu Feb 09, 2012 7:14 am
by ray.wurlod
Pivot.
Sort (if necessary).
Count.
Pivot.
Terse.
Yes.
Like your subject.
Posted: Thu Feb 09, 2012 9:29 am
by sudhana
Hi Ray,
will you please explain clearly....am not getting your point.
Posted: Thu Feb 09, 2012 6:29 pm
by ray.wurlod
What's not clear?
Posted: Thu Feb 09, 2012 10:17 pm
by RAJARP
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
Posted: Fri Feb 10, 2012 12:26 am
by sudhana
Hi Raja,
its working fine
Thank you somunch for your help..
Posted: Fri Feb 10, 2012 1:30 am
by vasubabu
thanks
Posted: Fri Feb 10, 2012 1:53 am
by ray.wurlod
Hijack alert!!!
You're running on two nodes, aren't you? Think about it.
Posted: Fri Feb 10, 2012 2:00 am
by pandeesh
For the input:
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
Posted: Fri Feb 10, 2012 2:22 am
by HendrikB
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 ...
Posted: Fri Feb 10, 2012 11:26 am
by gonuguntla
what is the stage variable logic here?
how variables required ?
Posted: Fri Feb 10, 2012 11:32 am
by pandeesh
gonuguntla wrote:what is the stage variable logic here?
Dear mr.Gonuguntla,
can you please start your own topic for your queries?
Thanks
Posted: Fri Feb 10, 2012 3:09 pm
by RAJARP
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