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.
Vertical Pivot using Looping transformer.
Moderators: chulett, rschirm, roy
Vertical Pivot using Looping transformer.
To succeed in life, you need two things: ignorance and confidence--Mark Twain
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
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
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.
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.
To succeed in life, you need two things: ignorance and confidence--Mark Twain