Converting column data into Columns and performing count

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
nikita.bandekar
Participant
Posts: 6
Joined: Mon May 06, 2019 6:42 am

Converting column data into Columns and performing count

Post by nikita.bandekar »

Hi , I am a newbie to datastage and stuck on this job since yesterday :(
Can someone help me with this

My data after transforming looks like below:

JobId Status
------------------
7859 Success
7859 Fail
7859 Success
7859 Success


I want my output data to look like:

JobId Success Fail
-------------------------
7859 3 1

The JobId does not differ and is consistent for all the records.

I tried aggregator stage but i am grouping on jobid, status and doing count rows which gives me the below output

JobId Status Count
--------------------------
7859 Success 3
7859 Fail 1
nikita bandekar
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Now all you need to do is to pivot the data. A Pivot stage should do it, or you could build the logic in a Transformer stage.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Right, that would be a "rows to columns" pivot, a.k.a. a vertical pivot.
-craig

"You can never have too many knives" -- Logan Nine Fingers
mouthou
Participant
Posts: 208
Joined: Sun Jul 04, 2004 11:57 pm

Re: Converting column data into Columns and performing count

Post by mouthou »

If you have a Transformer already in the job, you could use that with LOOPING logic inside the Transformer itself and get the count directly for each JobId (if you find Aggregator and Pivoting combination becomes heavy)
Post Reply