vertical pivot

[ds1user1]

I had done a good search on vertical pivot and got some information.But in every posts somebody is asking to search vertical pivot.Is there any particular post which is having complete information.

Thanks

[ds1user1]

Code:
Input----->Transformer---->Remove Duplicate----->Output


Declare stage variables as follows:
StageVar =DSLink3.No
StageVar1 =StageVar<> StageVar1
StageVar2 =StageVar
StageVar3 =DSLink3.Type:',':DSLink3.Name1:',': DSLink3.Name2
StageVar4 =IF StageVar2=1 then DSLink3.No:',': StageVar5 else Trim(StageVar4):',':Trim(StageVar5)

Output has two column with the below derivation:
No =DSLink3.No
Output =StageVar4
Check for the field length of the Output Column to be suficient to hold the concatenated data

CAN ANYBODY TELL ME WHAT IS StageVar5??

[chulett]

Maybe the author of whatever post you stripped this from? Since you didn't include that information or a link back to the original post, who knows?

Your problem is going to be the fact that any comprehensive post on the subject will be a Premium one nowadays, like this one. You could check the FAQ on the subject of creating multiple rows from a single input row, that's your basic 'vertical pivot' in a nutshell.

[ds1user1]

Hi criag

I am trying to write single row from the multiple rows.

Thanks

[ds1user1]

Sorry,Here is the complete post.

Sunshine2323 wrote:
Hi Smita,

Job Design:

Code:
Input----->Transformer---->Remove Duplicate----->Output


Declare stage variables as follows:
StageVar =DSLink3.No
StageVar1 =StageVar<> StageVar1
StageVar2 =StageVar
StageVar3 =DSLink3.Type:',':DSLink3.Name1:',': DSLink3.Name2
StageVar4 =IF StageVar2=1 then DSLink3.No:',': StageVar5 else Trim(StageVar4):',':Trim(StageVar5)

Output has two column with the below derivation:
No =DSLink3.No
Output =StageVar4
Check for the field length of the Output Column to be suficient to hold the concatenated data

Remove Duplicate Stage:
Pass the rows through a Remove Duplicate Stage and give the key as NO and select Last in the property Duplicate to Retain

Tested With:
Code:
1|A|AA|W
1|B|BB|N
1|C|CC|Y
1|D|DD|Z
2|A|AA|W
2|B|BB|N
2|C|CC|Y


Output:
Code:
1,A,AA,W,B,BB,N,C,CC,Y,D,DD,Z
2,A,AA,W,B,BB,N,C,CC,Y


Hope this helps

[chulett]

I am trying to write single row from the multiple rows.

Ok. A couple of different ways... one is concatenating several records into a single record using stage variables. It can be problematical because you need to 'write behind' as you go when the 'key' changes.

Or a simple Server solution is to use a hashed file if you have something appropriate to use as a key for each aggregated record. The same hashed file is used as a target and reference lookup. Doesn't exist - write new record to the hashed file. Does exist - append new content to existing content and write to the hashed file. When you are done, source from the hashed file.

[ray.wurlod]

Note that the last paragraph of the previous post is a SERVER solution as stated - you won't be able to use it in a parallel job.

Perhaps you could send a private message to Sunshine2323, the author of the post you quoted.

[Sunshine2323]

Hi,

Sorry, there was an error in writing the stage variable names

Here is the right way,

Code: Select all

StageVar=DSLink3.No
StageVar2=StageVar<> StageVar1
StageVar1=StageVar
StageVar5=DSLink3.Type:',':DSLink3.Name1:',': DSLink3.Name2
StageVar4=IF StageVar2=1 then DSLink3.No:',': StageVar5 else Trim(StageVar4):',':Trim(StageVar5)

I will also correct the original post.

Thank You,

[ds1user1]

Hi,
Thank You,

Thanks sunshine,This is really amazing.But if instead of dropping them in a single column and giving comma as seperator Can i drop them in seperate colums.

Thanks

[ray.wurlod]

You could, but you would probably find it easier to do that in a downstream stage.

[ds1user1]

You could, but you would probably find it easier to do that in a downstream stage. ...

CAN U PLEASE GUIDE ME THROUGH THIS.

tHANKS

[ray.wurlod]

You can use a Transformer stage or a Modify stage. As well as any "pass through" columns, you have the single column generated earlier on the input link, and multiple columns on the output link. Use appropriate function (probably the Field() function) to decompose the various pieces from the input column into the output columns.

[ds1user1]

You can use a Transformer stage or a Modify stage. As well as any "pass through" columns, you have the single column generated earlier on the input link, and multiple columns on the output link. Use ...

Thanks Ray, thats a good idea to implement but here i am facing one more issue.

using sunshine method : I had given

input

1|A|AA|W
1|B|BB|N
1|C|CC|Y
1|D|DD|Z
2|A|AA|W
2|C|CC|Y
2|D|DC|W

And i want the Output to be

1,A,AA,W,B,BB,N,C,CC,Y,D,DD,Z
2,A,AA,W,,,,C,CC,Y,D,DC,W

But with her method i am getting the output as

1,A,AA,W,B,BB,N,C,CC,Y,D,DD,Z
2,A,AA,W,C,CC,Y,D,DC,W

Any changes i need to do in this to achieve the desired output.

Thanks

[ray.wurlod]

Substantial changes. This is now a completely different requirement. You need to incorporate some algorithm (additional stage variables) to determine whether B is missing (and, presumably, whether A, C and D are missing).

See if you can design the algorithm on paper (in words - ignore the fact that you're using any particular tool). Specify exactly what you want, and everything that you want.

[ds1user1]

Substantial changes. This is now a completely different requirement. You need to incorporate some algorithm (additional stage variables) to determine whether B is missing (and, presumably, whether A ...

The actual requirement is like this Ray and tried it with sunshines method:

Input:


ID POS NA1 NA2
1 M AA XX
1 D GG KK
1 R AA XX
2 M AA YY
2 R HH XX
3 M AA XX
3 D GG XX
4 M AA YY
4 D GG XX
4 R AA XX
5 D AA XX
5 R BB XX
6 R AA XX
7 M BB XX
9 D CC XX
10 M AA XX
11 M AA XX
11 D AA XX
11 R AA XX

And the output should be:

ID POS_M NA1_M NA2_M POS_D NA1_D NA2_D POS_R NA1_R NA2_R
1 M AA XX D GG XX R AA XX
2 M AA YY - - - R HH XX
3 M AA XX D GG XX - - -
4 M AA YY D GG XX R AA XX
5 - - - D AA XX R BB XX
6 - - - - - - R AA XX
7 M BB XX - - - - - -
9 - - - D CC XX - - -
10 M AA XX - - - - - -
11 M AA XX D AA XX R AA XX

Can you suggest me how i need to proceed,it is my first assignment.Any help is appreciated

Thanks