if i have more than 10 keys columns to vertical pivot logic

monaz · Post by **monaz** » Fri Apr 17, 2009 9:40 pm

hi All,

I have a requirement in which i have 10 keys columns and i need to perform vertical pivot conversion for the data.

Please suggest..

Here is the example

INPUT:-

AKey BKey CKey DKey EKEY FKEY GKEY HKEY IKEY JKEY SeqNoKEY ADD
1 2 11 a b c d e f g 1 A
1 2 11 a b c d e f g 1 P
1 2 11 a b c d e f g 1 P
1 2 11 a b c d e f g 1 L
1 2 11 a b c d e f g 1 E
OUTPUT:-
AKey BKey CKey DKey EKEY FKEY GKEY HKEY IKEY JKEY ADD
1 2 11 a b c d e f g APPLE

I tried using the using stage variables in transformer stage and passing to Remove duplicate stage and using the below logic
StgVarCurrent;- (AKey: BKey: CKey: DKey: EKEY; FKEY: GKEY: HKEY: IKEY: JKEY)

stgvarpervious:- StgVarcurrent

StgVarADD:- ADD

StgVarlogic- If StgvarCurrent<> StgVarPerious then StgVarlogic else StgVarADD:StgValogic

Please correct me if my logic is wrong and i am not getting the required output

Please note i have not used Seq_no anywhere in my logic

Thanks

sbass1 · Post by **sbass1** » Fri Apr 17, 2009 11:44 pm

See viewtopic.php?t=126366&highlight=

ray.wurlod · Post by **ray.wurlod** » Sat Apr 18, 2009 2:19 am

What output ARE you getting?

There's no limit on the number of pivot keys - you just have to handle them all.

chulett · Post by **chulett** » Sat Apr 18, 2009 7:21 am

And their number doesn't change the "how" of that processing.

throbinson · Post by **throbinson** » Mon Apr 20, 2009 6:10 am

The order of the Stage variables is of paramount importance. The number of keys is not. If the order is as you've listed them above then the "IF" stage variable will never detect a key change. The "current" comes first followed by the stage variable that detects the key change and then the assign "current" to "previous" stage variable. It should be intuitive that your input data must be sorted in key(s) order as well.

monaz · Post by **monaz** » Thu Apr 23, 2009 8:54 am

throbinson wrote:The order of the Stage variables is of paramount importance. The number of keys is not. If the order is as you've listed them above then the "IF" stage variable will never detect a key change. The "current" comes first followed by the stage variable that detects the key change and then the assign "current" to "previous" stage variable. It should be intuitive that your input data must be sorted in key(s) order as well.

Thanks to all,

Now the problem is that after sorting the data in the sorter stage i am not getting correct format data.

since i have 10key columns can i sort in any order. i mean key3 can i keep at keycolumn10 th position is it possible?

Please advice

ray.wurlod · Post by **ray.wurlod** » Thu Apr 23, 2009 2:36 pm

Ordinal position of the key columns in the key is independent of the order that you specify that they be sorted.

throbinson · Post by **throbinson** » Thu Apr 23, 2009 2:51 pm

Yeah, what he said.
Now, you know you'll have to handle the outliers as special cases.
For example;
1. The very first row in won't match prev but may not have to be written out immediately.
2. The very last row in needs to be triggered to write it out, even if curr matches prev.