Reverse Pivot
Moderators: chulett, rschirm, roy
Reverse Pivot
Newbie question - Can anybody tell me how to accomplish what the PIVOT stage does but in reverse.
Input:
rec1: key1, fld1
rec2: key1, fld1
rec3: key1, fld1
rec4: key2, fld1
rec5: key2, fld1
...
OutPut:
rec1: key1,rec1.fld1,rec2.fld1,rec3.fld1
rec2: key2,rec4.fld1,rec5.fld1,,
...
The process should be able to handle some decision making like what output bucket to populate on certain criteria.
Any help, in detail, would be VERY appreciated
Input:
rec1: key1, fld1
rec2: key1, fld1
rec3: key1, fld1
rec4: key2, fld1
rec5: key2, fld1
...
OutPut:
rec1: key1,rec1.fld1,rec2.fld1,rec3.fld1
rec2: key2,rec4.fld1,rec5.fld1,,
...
The process should be able to handle some decision making like what output bucket to populate on certain criteria.
Any help, in detail, would be VERY appreciated
-
- Premium Member
- Posts: 7
- Joined: Wed Nov 02, 2005 12:19 pm
The databases I work with are deep not wide, so I do this a lot.
Doing this in a parallel manner with a generic routine is difficult. I have done this with a C routine plug in. The trick is to get all the columns for the same record into the same partition. I do this by hash/sort partitioning on the key field (and only the key field) into the plugin.
Some of the things you need to know when doing this are
- keyid
- output order (unless you have a row for every column and you can garantee they are in the correct order)
- format (TEXT, VALUE, NUMBER, DATE) (If you need to use quotes, or need to format the data )
- column value
Good luck[/list]
Doing this in a parallel manner with a generic routine is difficult. I have done this with a C routine plug in. The trick is to get all the columns for the same record into the same partition. I do this by hash/sort partitioning on the key field (and only the key field) into the plugin.
Some of the things you need to know when doing this are
- keyid
- output order (unless you have a row for every column and you can garantee they are in the correct order)
- format (TEXT, VALUE, NUMBER, DATE) (If you need to use quotes, or need to format the data )
- column value
Good luck[/list]
Thanks
Larry
Larry
If your input data is sorted by key and fld1 then you can use the "classical" approach as done in Server jobs. Put in a transform stage and use stage variables to store your interim output columns and last column data data, set the constraint to only output rows when the key-fld1 changes and output the collected list of key and fld1 combinations.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Hi Arndw
You will know the key changed only when you read the row where the key changed so ideally
rec1: key1, fld1
rec2: key1, fld1
rec3: key1, fld1
rec4: key2, fld1
rec5: key2, fld1
Only when i read in rec4 Key2,fld1 i will know the key has changed so if you want me to write out what i have been holding till then in the stage variables in to the output link it will not work for the last record because you will never know when to write it.
i am trying with a couple of sort stages logic to find the key change the claasical approach. I have an other idea of writing everything out and then using the remove duplicates to retain the last row which will have the most consolidated information.
Thanks
Pavan
You will know the key changed only when you read the row where the key changed so ideally
rec1: key1, fld1
rec2: key1, fld1
rec3: key1, fld1
rec4: key2, fld1
rec5: key2, fld1
Only when i read in rec4 Key2,fld1 i will know the key has changed so if you want me to write out what i have been holding till then in the stage variables in to the output link it will not work for the last record because you will never know when to write it.
i am trying with a couple of sort stages logic to find the key change the claasical approach. I have an other idea of writing everything out and then using the remove duplicates to retain the last row which will have the most consolidated information.
Thanks
Pavan
Why dont you think it will work again? Keep apending the values to the stage variable untill a key change is encountered. In that case reset the stage variable and start appending untill the next key change is encountered. Do this till the last record. Then pass the output to remove duplicate stage and retain the last record for each id.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
RE: Reverse Pivoting
Hi Arndw and DSguru2B,
I understand your concept and I have done something very similar. I am looking for a Parallel solution but I will definitely keep this in mind.
I do not know the other person who posted under UPS. I am the original one who posted as UPS.
Thanks for your help.
John - jalden@ups.com
I understand your concept and I have done something very similar. I am looking for a Parallel solution but I will definitely keep this in mind.
I do not know the other person who posted under UPS. I am the original one who posted as UPS.
Thanks for your help.
John - jalden@ups.com
RE: Reverse Pivoting
DSguru2B,
What stage would I use then? Would I use a C routine as someone previously suggested?
Regards -
John
What stage would I use then? Would I use a C routine as someone previously suggested?
Regards -
John
RE: Reverse Pivoting
DSguru2B,
Thanks - I will try this option and keep you appraised.
Thanks again.
John
Thanks - I will try this option and keep you appraised.
Thanks again.
John