Converting multiple records into one

kanasai167 · Post by **kanasai167** » Thu Mar 29, 2012 6:59 am

I got the data like this :
ID,Code,Value
001,item1,a1
001,item3,a2
001,item4,a3
001,item8,a4
002,item1,b1
002,item5,b2
002,item6,b3

My expected output is :
ID,Item1,Item2,Item3,Item4,Item5,Item6,Item7,Item8
001,a1,Null,a3,a4,Null,Null,Null,a4
002,b1,Null,Null,Null,b2,b3,Null,Null

Is it possible to do in transformer stage using loop or stage variable ??
I read some thread on similar scenario mentioned about pivot stage , is it only available in Data Stage , because i using Quality Stage 8.5 and can't find this item.

Thank you.

Mike · Post by **Mike** » Thu Mar 29, 2012 8:42 am

A transformer with stage variables is good enough with properly partitioned and sorted input... you can take advantage of the LastRowInGroup transform function for your output constraint.

Mike

qt_ky · Post by **qt_ky** » Thu Mar 29, 2012 4:13 pm

Transformer stage loop should work as well. The product documentation has good loop examples.

kanasai167 · Post by **kanasai167** » Thu Mar 29, 2012 5:49 pm

qt_ky wrote:Transformer stage loop should work as well. The product documentation has good loop examples.

You mean this ? "Loop example: multiple repeating values in a single field"

Actually my data is already in db2 format , each value represent a value within a column , no longer a comma-separated values.

Anyway to capture it ? Because the example is separate the value by detecting "/".

Thanks

ray.wurlod · Post by **ray.wurlod** » Thu Mar 29, 2012 5:59 pm

Can you show us what your data DO look like?

kanasai167 · Post by **kanasai167** » Thu Mar 29, 2012 7:59 pm

ID Code Value
1 Item1 a1
1 Item3 a2
1 Item4 a3
1 Item8 a4
2 Item1 b1
2 Item5 b2
2 item6 b3

Like this , in DB2

ray.wurlod · Post by **ray.wurlod** » Thu Mar 29, 2012 8:11 pm

This looks like a simple variant of vertical pivot, which you can achieve with the PXPivot stage.

kanasai167 · Post by **kanasai167** » Thu Mar 29, 2012 8:33 pm

I see. But i'm using QualityStage 8.5 and i believe Pivot Stage is not include in QualityStage right ?

Is there any workaround to perform task like Pivot Stage under transformer stage ?

Thanks.

qt_ky · Post by **qt_ky** » Thu Mar 29, 2012 8:48 pm

QualityStage adds "data quality" stages to your DataStage Designer palette. If you check under the "processing" palette category, you should find the Pivot stage. You can mix and match DS & QS stages in the same job design.

ray.wurlod · Post by **ray.wurlod** » Thu Mar 29, 2012 8:52 pm

Further, you posted your question in a DataStage forum.

qt_ky · Post by **qt_ky** » Thu Mar 29, 2012 8:57 pm

Regarding the loop, a little further down there is another example, "Input row group aggregation included with input row data." Not that you want to aggregate in your case, but I'm just saying I think what you want to do is possible with a loop. You can get very creative with loops. However, they can become convoluted, so using a Pivot stage is going to be more straight-forward! It won't matter if your input comes from a file or from DB2.

kanasai167 · Post by **kanasai167** » Thu Mar 29, 2012 9:39 pm

Ray ,
i know this section is about DataStage , however i believe my current scenario is more towards DataStage rather than QualityStage. And i already stated that im using QualityStage 8.5 in my first post.

Eric ,
actually i am not familiar on how the transformer loop works but i will try it out.
Currently i tried to achieve it as below :

First , i transform my data into something like this :
ID ITEM1 ITEM2 ITEM3 ITEM4 ITEM5
1 a1 "" "" "" ""
1 "" a2 "" "" ""
1 "" "" "" "" a3

Then i output it into a aggregator stage to select the Max values of each column so my data will be something like this :
ID ITEM1 ITEM2 ITEM3 ITEM4 ITEM5
1 a1 a2 "" "" a3

However, after i found out that aggregator stage only support Integer/Decimal value ? Some of my data which contains char become 0 at the output stage.

kanasai167 · Post by **kanasai167** » Thu Mar 29, 2012 9:44 pm

qt_ky wrote:QualityStage adds "data quality" stages to your DataStage Designer palette. If you check under the "processing" palette category, you should find the Pivot stage. You can mix and match DS & QS stages in the same job design.

I look through "Data Quality" and "Procssing" palette but i could not find Pivot Stage.
And i do a quick find on Pivot then i found a Pivot and Pivot Entreprise Stage. But it is Read Only and i could not drag it into my job.

Thanks

qt_ky · Post by **qt_ky** » Fri Mar 30, 2012 6:06 am

Do you have a local admin to check with? How about support? Sounds like you've got problems...

Try right-clicking on the palette and exploring options to load defaults or reset to factory default settings.

If your job type is Parallel job, then the Processing category should show Pivot and Pivot Enterprise stage types, and allow you to put those into your job design.

I suggest you focus on getting a Pivot stage to work rather than going down the loop path.

kanasai167 · Post by **kanasai167** » Fri Mar 30, 2012 6:49 am

already solve the problem using loop condition under transformer stage.

i make the data into :

ID,Item1,Item2,Item3,Item4,Item5,Item6,Item7,Item8
001,a1,Null,null,null,Null,Null,Null,null
001,a1,Null,a2,null,Null,Null,Null,null
001,a1,Null,a2,a3,Null,Null,Null,null
001,a1,Null,a2,a3,Null,Null,Null,a4

then filter last row of each group using NumSavedRows and NumRows provided in product documentation.