Skip navigation
Contact Us
1855 Views 7 Replies Latest reply: Jan 29, 2013 12:58 AM by Manojkumar Venkatachalam RSS
Abinash Jena Novice 47 posts since
Apr 28, 2011
Currently Being Moderated

Jan 12, 2012 3:13 AM

Scenario : for flat file

I have a flat file and the data is like this…only fixed length record

____________________
0mps324235dfgdfgh4563
1mps2342345egd34334
2mps2345345wefqw123
Emps231342egweghw23345
3mps2313sdfgsdfgwe4
5mps231334fgsadgs
0mps324fgs345345
1mps2342345egd34334
2mps2345345wefqw123
Emps231342egweghw23345
3mps2313sdfgsdfgwe4
5mps231334fgsadgs


Data comes row by row…
Each row is has a limit of 650 characters but the data may be less than 650....
Each row has different fixed length..…
It mainly depends on the first position (I mean 1st digit … like 0,1,2,E  as given in the data)


My output should look like.. IN SQL SERVER

-----------------
seq no      activity_group      type      record data

1                 1                     0           0mps324235dfgdfgh4563
2                 1                     1           1mps2342345egd34334
3                 1                      2           2mps2345345wefqw123
4                 1                      E           Emps231342egweghw23345
5                 1                     3            3mps2313sdfgsdfgwe4
6                 1                     5           5mps231334fgsadgs
7                 2                     0           0mps324fgs345345
8                 2                     1           1mps2342345egd34334
9                 2                     2            2mps2345345wefqw123
10               2                     E           Emps231342egweghw23345
11               2                     3           3mps2313sdfgsdfgwe4
12               2                     5           5mps231334fgsadgs

 

Basically how to find activity_group with repect to record data ?
 

  • Mariappan Kannapiran Apprentice 143 posts since
    Jan 29, 2011
    Currently Being Moderated
    Jan 12, 2012 3:41 AM (in response to Abinash Jena)
    Scenario : for flat file

    It seems Activity Group is related to your business.The activity group gets increase by one after pattern for the letter of evrey row ends up with this 0,1,2,E,3,5

     

    Ignore my comments if it doesn't suits you

  • sasi ramesh Master 1,110 posts since
    Oct 2, 2010
    Currently Being Moderated
    Jan 12, 2012 3:44 AM (in response to Abinash Jena)
    Scenario : for flat file

    Hi Abinash,

     

    This can be done with the help of expression transformation.In target DB level make the seqval column as identity so that it will increment for each insertion row. We have to split the first character for each records and to compare whether it is occuring again or not.If occurs then we need to increment the activity value .

     

    Use the below logic to in expression.I have not implemented just giving my thoughts which is in top of my mind.

     

    (variable- string)V_FirstChar = Substr(col1 ,1,1)

    O_Type = V_FirstChar

    (variable - int ) V_Counter = V_Counter+1

    (variable - string) V_Value = IIF( V_Counter=1,V_FirstChar,V_Value) (this is to hold the first character for the initial group i.e.  0)

    (variable - int) V_Activity = IIF( V_Value != V_FirstChar,V_Activity,V_Activity+1)

    O_Activity = V_Activity

     

     

    Regards

    Sasiramesh

    • Jaspreet Banga Expert 526 posts since
      Jun 14, 2011
      Currently Being Moderated
      Jan 13, 2012 3:02 AM (in response to sasi ramesh)
      Scenario : for flat file

      HI sasi,

       

      one doubt i am facing is that ,V_Value = IIF( V_Counter=1,V_FirstChar,V_Value)  that this logic will only increment value if the incoming v_value is 0, bcoz V_Activity = IIF( V_Value != V_FirstChar,V_Activity,V_Activity+1)  v_activity is only incremented when both record matches ,and as per logic the v_value will always hold 0 bcoz the counter value is 1 only for the 1st record, so i doubt weather there some slight change required in the logic.. please correct/explain me if i getting wrong at some point..

       

      Thanks,

      Jaspreet

  • sushil adhikari Novice 86 posts since
    Jun 20, 2012
    Currently Being Moderated
    Sep 17, 2012 8:16 AM (in response to Abinash Jena)
    Scenario : for flat file

    hi jena u can achive this with following way also

     

    first extract the first character from the record

     

    then sort it

     

    so that data becomes

     

    0

    0

    1

    1

    2

    2 and so on

     

    then create group through variable

     

    grroup=current_char!=prev_char,1,grroup+1

     

    then again sort on group value

     

    let me know if u faceing any problem with this approach

  • Manojkumar Venkatachalam Novice 82 posts since
    Dec 18, 2012
    Currently Being Moderated
    Jan 29, 2013 12:58 AM (in response to Abinash Jena)
    Re: Scenario : for flat file

    Hi Abhinash ,

     

    Create an Expression Transformation with following Ports :

     

    • RECORD_DATA - Input / Output Port
    • V_TYPE - Variable Port containing the Expression SUBSTR ( RECORD_DATA , 1, 1 )

     

    • V_ACTIVITY - Variable Port containing the Expression below :

                  IIF ( V_TYPE = 0 , V_ACTIVITY + 1 , V_ACTIVITY )

    • O_SEQ_NO - Output Port containing  SEQ_NO from Sequence Transformation created.
    • O_TYPE - Output Port containing the Value of V_TYPE
    • O_ACTIVITY - Output port containing the Value of V_ACTIVITY

    More Like This

    • Retrieving data ...

    Bookmarked By (0)