Vertical Pivot using Looping transformer.

sksdsx · Post by **sksdsx** » Thu Jul 10, 2014 8:32 pm

Hi ,

--- Incoming Source Data

NTFCTN_ID NTFCTN_DT EVENT_TYPE SEQ_NUM NAME VALUE
---------- -------------------- ---------- ---------- --------------- ---------------
311 29-JUN-2014 12:49:38 31 1 EXTRA1 Extra
311 29-JUN-2014 12:49:38 31 2 STATE Florida
311 29-JUN-2014 12:49:38 31 3 CITY Jacksonville
312 30-JUN-2014 12:49:39 31 1 CITY Atlanta
312 30-JUN-2014 12:49:39 31 2 STATE Georgia
312 30-JUN-2014 12:49:39 31 3 CITY Houston
312 30-JUN-2014 12:49:39 31 4 EXTRA1 Extra
312 30-JUN-2014 12:49:39 31 5 STATE Texas
312 30-JUN-2014 12:49:39 31 6 CITY Miami
312 30-JUN-2014 12:49:39 31 7 STATE Florida
313 01-JUL-2014 12:49:40 31 1 STATE California
313 01-JUL-2014 12:49:40 31 2 CITY Los Angeles
313 01-JUL-2014 12:49:40 31 3 CITY Seattle
313 01-JUL-2014 12:49:40 31 4 STATE Washington

14 rows selected.

--- DESIRED FINAL OUTPUT

NTFCTN_ID NTFCTN_DT EVENT_TYPE SET_NUM CITY STATE
---------- -------------------- ---------- ---------- --------------- ---------------
311 29-JUN-2014 12:49:38 31 1 Jacksonville Florida
312 30-JUN-2014 12:49:39 31 1 Atlanta Georgia
312 30-JUN-2014 12:49:39 31 2 Houston Texas
312 30-JUN-2014 12:49:39 31 3 Miami Florida
313 01-JUL-2014 12:49:40 31 1 Los Angeles California
313 01-JUL-2014 12:49:40 31 2 Seattle Washington

I am trying to acheive this using looping transformer but I am lost as this seem to be nested Loop first for Notification_id and then within
that for 2 rows of Name Value combination for 'CITY' and 'STATE'.

Can someone advice me I would appreciate any help.

Thanks In Advance.

ssnegi · Post by **ssnegi** » Thu Jul 10, 2014 10:07 pm

First filter Extra from the data.
Then in sequential transformer sort NTFCTN_ID ascending,SEQ_NUM ascending.
Then use stage variables :
svSeqNo initial value 0 : if input.svNTFCTN_ID = svNTFCTN_IDold then svSeqNo+1 else 1
svNTFCTN_IDold : input.svNTFCTN_ID
svPivot : if mod(svSeqNo,2) = 0 then 1 else 0
svSeqNo1 initial value 0 : if svPivot = 1 then svSeqNo1 + 1 else 0
svState : if input.NAME = 'STATE' then input.value else svState
svCity : if input.NAME = 'CITY' then input.value else svCity
Output Column
SEQ_NUM - svSeqNo1
STATE - svState
CITY - svCity
Constraint : svPivot = 1

sksdsx · Post by **sksdsx** » Thu Jul 10, 2014 10:32 pm

Thanks aton! I will try and let you the result set.

sksdsx · Post by **sksdsx** » Thu Jul 10, 2014 11:45 pm

Thanks a ton for your time and patience ...This really helped.

All the values are getting populated correctly, except SEQ_NUM...the value is set to 1 always

sksdsx · Post by **sksdsx** » Fri Jul 11, 2014 12:04 am

Hi , I used another transformer stage and checked in incoming Notification id and previous notification are same then incremented the counter by 1 else set it 0 and was able to get the o/p correctly.

I am good not sure why couldn't achieve it in same transformer ...Had set (preserve-partitioning flag is propagate)

and partitioning as Same ..later had tried by running the all stages in sequential mode. Nyways thanks a ton for your suggestion!!! will mark this topic as resolved.