DSXchange

Hi,
I have the requirement to convert rows to columns.So,i used vertical pivot stage.But it works when the number of records for each key are same.But,my input is as follows.
col1,col2
1,C1
1,C2
1,C3
2,C1
2,C3

So,output should be
1,C1,C2,C3
2,C1,Null,C3
Please suggest me how can i do this in vertical pivot stage

Hi,
I came across the post saying viewtopic.php?t=145198 that it can be done in a transformer using loop variables.Could anyone please give an idea how can i achieve this? and get the following output.
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

Deepa, welcome back again

We have discussed almost something similar in the below link.

viewtopic.php?t=144616&highlight=

I have never heard of Vertical Pivot stage in any DataStage version. There is a pivot stage which can do both horizontal and vertical pivots from version 8.5. Earlier versions can do horizontal pivots only, though vertical can be achieved in transformer stage.

kandyshandy wrote:I have never heard of Vertical Pivot stage in any DataStage version. .

Hi Kandy,
I mean doing vertical pivot in pivot stage only.
Actually the earlier post was to pivot the source data irrespective of number of records.
now,i need to consider maximum number of records (in sample data,it have shown 3.actually it is 45 in my requirement)and insert null if a record is not present while pivoting.

Your sample should be more realistic... how do I know that C2 is missing in the following sample? or how do I know that second record is missing for this group?

2, C1
2, C3

From the source i will be getting 3 columns
F_N,element_name,value
1,ID,13
1,Name,xx
1,Place,yy
2,ID,16
2,Place,zz

The maximum number of element_names for key column F_N is 3.
So,output should be
F_N,ID.Name,Place
1,13,xx,yy
2,16,Null,zz

Just thinking out loud as don't know how well it will work...

You could generate x number of rows to match all your possible element_name field values (i.e. id, name, place etc), do an outer join to your source so that each F_N joins to every generated row, this will then ensure every F_N has every possible element. From there do your vertical pivot.

The only hiccup springing to mind is binding the F_N to the generated rows...

That's a nice requirement Deepa

Thanks Kryt0n.I will try the above logic and let you know.

Sort and partition by F_N.
In stage variables:

• Map ID to svID if element_name is "ID" otherwise leave svID untouched.

  Code: Select all

  svID <== If InLink.element_name = "ID" Then InLink.ID Else svID

  Map Name to svName if element_name is "Name" otherwise leave svName untouched.
  
  Map Value to svValue if element_name is "Value" otherwise leave svValue untouched.

Use LastRowInGroup() function to constrain the output link. Derive the output columns from the stage variables.

If you have an earlier version than 8.5 follow the Transformer stage with a Remove Duplicates stage, group on F_N and preserve Last.

Deepa... Here is something you can try but one additional requirement is highlighted in bold

This is purely from thought process and never been tested.

Sample Input:
1,ID,13
1,Name,xx
1,Place,yy
S,SSSS,SS --> (Add this dummy row to your input file as last row)

Requisites:
Ensure that the records with same F_N is in one partition & ordered.

Stage Variables:
CurrID = Col1
WriteCheck = [If CurrID <> PrevID Then 'WRITE' Else 'DROP']
svPrevCol1 = svCol1
svPrevCol2 = svCol2
svPrevCol3 = svCol3
svPrevCol4 = scCol4
PrevID = CurrID
svCol1 = F_N
svCol2 = [If Col2 = 'ID' Then Col3 Else svCol2]
svCol3 = [If Col2 = 'Name' Then Col3 Else svCol3]
svCol4 = [If Col2 = 'Place' Then Col3 Else svCol4]

Constraint:
@INROWNUM <> 1 and WriteCheck = 'WRITE'

Mapping:

svPrevCol1 -> Col1
svPrevCol2 -> Col2
svPrevCol3 -> Col3
svPrevCol4 -> Col4

All the best

Recent versions of DataStage add a mechanism (system variable?) to detect the last record in your input along with some other useful ones. Off the top of my head I don't recall their exact names. This to avoid having to add any kind of 'dummy' row.

Names of the Transformer stage functions are:

LastRowInGroup()

LastRow()

I believe these were first introduced in version 8.5.

Yes Eric

Hi,
This is resolved.I tried the following.

records with same F_N is taken in one partition by selecting appropriate partioning method.

Stage Variables:
next = F_N
ic2=if (next<>prev) then '' else c2
ic1=if (next<>prev) then '' else c1
c1=if ELEMENT_NAME='ID' then VALUE else ic1
c2=if ELEMENT_NAME=Name' then VALUE else ic2
prev=next

Constraint:
LastRowInGroup(F_N)
output columns:
ID=c1
Name=c2
File_Name=F_N