vertical pivot

smitageorge · Post by **smitageorge** » Tue Mar 14, 2006 12:08 pm

Hi all

I have been looking at this particular post in the server

viewtopic.php?t=97681&highlight=vertical+pivot

I have seen Rays and jims replies but can u guys throw some light when we are working with parallel(As we don't have hash file here) how to implement this.

Thanks
smita

DSguru2B · Post by **DSguru2B** » Tue Mar 14, 2006 12:13 pm

1.You can use a transformer to unpivot the source.
2. We have lookup files sets and Data sets. These can be used for lookups.

kcbland · Post by **kcbland** » Tue Mar 14, 2006 12:26 pm

PX has no update-able reference data set capability. For PX, you're going to have to either write a custom buildop or use the obscure Make Vector stage to do what you need.

smitageorge · Post by **smitageorge** » Tue Mar 14, 2006 2:53 pm

kcbland wrote:PX has no update-able reference data set capability. For PX, you're going to have to either write a custom buildop or use the obscure Make Vector stage to do what you need. ...

Can you help me with that ken.

Thanks
smita

kcbland · Post by **kcbland** » Tue Mar 14, 2006 3:06 pm

Not right now.

I'm still working on developing a Bag of Tricks for PX, which could include such functionality. But it's not ready to share.

Unless you have obscene volumes, you could still use a Server job prep the data using a hash file, and then dump it out to a sequential file. Then, use a PX job to feed that file into a dataset.

A mixed bad approach is my recommendation for PX sites. Use the tools appropriate, if it's a shell script, SQL script, Server job, PX job, or blend of those.

ray.wurlod · Post by **ray.wurlod** » Tue Mar 14, 2006 3:58 pm

For anyone comfortable with multi-valued fields (for example in UniVerse or UniData), the Make Vector stage should not be daunting. A vector is simply a list of values in the one field.

smitageorge · Post by **smitageorge** » Tue Mar 14, 2006 4:22 pm

kcbland wrote: A mixed bad approach is my recommendation for PX sites. Use the tools appropriate, if it's a shell script, SQL script, Server job, PX job, or blend of those.

I understand that but i am not allowed to use server at the site.

Thanks
smita

kcbland · Post by **kcbland** » Tue Mar 14, 2006 4:36 pm

Make Vector is your best option at this point. When you perfect it, please post the solution for others.

DSguru2B · Post by **DSguru2B** » Wed Mar 15, 2006 9:59 am

What do you exactly want to do. I have come across unpivoting horizontal rows ( horizontal pivot). And have handled them without using the vector stage. Post what you require to do?

ray.wurlod · Post by **ray.wurlod** » Wed Mar 15, 2006 10:30 am

smitageorge wrote:
kcbland wrote: A mixed bad approach is my recommendation for PX sites. Use the tools appropriate, if it's a shell script, SQL script, Server job, PX job, or blend of those.
I understand that but i am not allowed to use server at the site.

Thanks
smita

Challenge that decision. It is based on a false premise, which you can find by reading recent posts here about server jobs (not) going away.

smitageorge · Post by **smitageorge** » Wed Mar 15, 2006 12:08 pm

DSguru2B wrote:Post what you require to do?

Exactly same way as asked by sengs.

NO ........... TYPE
1 ....................A
1.................... B
1.................... C
1.................... D

At the end my requirement is,
1.........................A,B,C,D

Thanks
smita

DSguru2B · Post by **DSguru2B** » Wed Mar 15, 2006 12:25 pm

To my understanding:

Your Src:
NO ........... TYPE
1 ....................A
1.................... B
1.................... C
1.................... D

Create Temp with metadata like this

NO ... Type1...Type2...Type3...Type4
1 ..... A......... B .........C ......... D

Map the each of them to the temp file.

Then if you want them together depending on your requirement, concatenate type1:type2:type3:type4 and map them again to the target.
For this design you need to know the Max number of types you are going to have as this design would static metadata handled.

If you dont know the max types then you will have to write a routine to handle them. Or someone might come up with another option.
Good Luck

Sunshine2323 · Post by **Sunshine2323** » Wed Mar 15, 2006 12:51 pm

Hi Smita,

Can be easily be done using stage variables.
You can search the forum and find how to implement this using stage variables.
In case you do not find it I will post it by tomorrow morning.

smitageorge · Post by **smitageorge** » Wed Mar 15, 2006 1:24 pm

[quote="DSguru2B"]To my understanding:
quote]

My exact requirement is like this:

source:

NO TYPE NAME1 NAME2
1 A AA W
1 B BB
1 C CC Y
1 D DD Z

Create Temp with metadata like this

NO Type1 Type2 Type3 Type4 Type5 Type6 Type7 Type8 Type9 ....
1 A AA W B BB C CC Y ....

Thanks
smita

smitageorge · Post by **smitageorge** » Wed Mar 15, 2006 1:25 pm

Sunshine2323 wrote:Hi Smita,

Can be easily be done using stage variables.
You can search the forum and find how to implement this using stage variables.
In case you do not find it I will post it by tomorrow morning.

Hi amruta,

I tried the stage variable but might be you are having some good understanding of this .Please post it .

Thanks
smita