Page 1 of 1
Vertical Pivot without any grouping column
Posted: Sun Oct 07, 2012 8:19 am
by abhik05
I have input data coming as :
In_Field A
-----------
1a
2e
3f
7h
8t
.
.
.
n
I need to map this input field value to 3 separate output fields.
Ot_FieldA Ot_FieldB Ot_FieldC
--------------------------------------
1a 2e 3f
7h 8t ....
... .... ....
I dont have any input field to group by the set (3 rows).
Please help.
Abhik.
Posted: Sun Oct 07, 2012 8:59 am
by chulett
Create a grouping column then pivot. It can be as simple as a number you increment every 3 rows and then drop post-pivot. Be careful with your partitioning, of course. If you are on 8.5+ you could use transformer looping for this rather than the Pivot stage.
Posted: Sun Oct 07, 2012 9:32 am
by bhasds
Hi Abhik05,
You may try this through transformer-
step 1-
take 2 stage variable-
Code: Select all
If DSLink105.col1 <> SV1 Then SV1:" ": DSLink105.col1 Else @FALSE SV1
@PARTITIONNUM + (@NUMPARTITIONS * (@INROWNUM - 1)) + 1 SV2
step2- In derivation constraint-
step3- deriavtion
Code: Select all
Field(Field(SV1," ", (Dcount(SV1," ")-3)+1, Dcount(SV1," "))," ",1) col1
Field(Field(SV1," ", (Dcount(SV1," ")-3)+1, Dcount(SV1," "))," ",2) col2
Field(Field(SV1," ", (Dcount(SV1," ")-3)+1, Dcount(SV1," "))," ",3) col3
Posted: Mon Oct 08, 2012 9:43 am
by PhilHibbs
If you're going with a Transformer based approach, remember to include "Or LastRow()" in the output constraint in case your data doesn't have an exact multiple of three rows.
I'm not sure I like the idea of concatenating with spaces and then splitting. I'd rather gather the values in three Stage Variables. Just because the example doesn't have spaces, doesn't mean that the data never will. Why not concatenate with and split on an 'x' because, after all, none of the examples have an 'x' in them!
Also, I feel the need to reiterate chulett's warning about partitioning. The premise of your problem practically precludes partitioning, so you may have to use Sequential execution mode, a Node Map constraint, or partitioning on a fixed value.