Skip navigation
Contact Us
10128 Views 25 Replies Latest reply: Mar 27, 2013 6:06 AM by Viral Raichura RSS
prabhu balakrishnan Novice 36 posts since
Jun 1, 2011
Currently Being Moderated

Jul 12, 2011 2:43 AM

unique & duplicate records

I have a scenario like my source have duplicate values, unique values move into tgt1 and duplicate values move into tgt2. kindly explain brefily. possible send screen shots.

 

 

Thanks in advance

 

 

 

 

 

 

 

I have practise in the above logic but is not showing dup & non dup

Attachments:
  • Veeru B Master 1,242 posts since
    Sep 27, 2010
    Currently Being Moderated
    Jul 12, 2011 3:01 AM (in response to prabhu balakrishnan)
    unique & duplicate records

    Hi Prabhu,

     

    Yes this is possible using informatica.

     

    you can try in below approach...

     

    SRC-->SQ---> SRT--->EXP-->RTR--->TGT

     

    in sorter transformation sort data based on dilapidate ID.

     

    In expression take one variable port for storing previous id.

    Ex:

     

    in_ID

    in_NAME

    v_FLAG=IIF(in_ID<>v_ID,1,2)

    v_ID=in_ID

    out_FLAG=v_FLAG

     

    In router create two groups one for valid and another one for duplicate records

     

    NON DUPLICATE=(out_FLAG=1)

    DUPLICATE=(out_FLAG=2)

     

    Let me know if further details required.

     

    Regards,

    Veeru

      • Veeru B Master 1,242 posts since
        Sep 27, 2010
        Currently Being Moderated
        Jul 12, 2011 3:17 AM (in response to prabhu balakrishnan)
        Re: unique & duplicate records

        Hi Prabhu,

         

        this is how this condition will work.

         

        in expression you have two in put ports and two variable ports and on output port

        input:

        in_ID

        in_NAME

        v_FLAG

        v_ID=in_ID

        out_FLAG

         

        Lets take example for this works.

         

        if you have source data

         

        1,AAA

        2,CCC

        3,DDD

        1,BBB

        4,FFF

        3,EEE

         

        After sorte out put data looks like this

         

        1,AAA

        1,BBB

        2,CCC

        3,DDD

        3,EEE

        4,FFF

         

        in expression for the first record

        in_ID=1

        in_NAME=AAA

        v_FLAG=(IIF(v_ID<>in_ID,1,2)=1

        v_ID=in_ID

         

        (IIF(v_ID<>in_ID,1,2):---

        this evaluate like check for the previous id with present id if both are not equal then pass 1 out put else if present id equal to previous id then pass 2.

        initially v_ID don't have any value so it will pass 1 output.

         

        Now for second record

         

        in_ID=1

        in_NAME=BBB

        v_FLAG=(IIF(v_ID<>in_ID,1,2)

        for this record we have previous value as 1, so the output of this expression is 2.

        like that it will flag for all records.

         

        i Hope i am able to understand you.

         

        Regards,

        veeru

        • Veeru B Master 1,242 posts since
          Sep 27, 2010
          Currently Being Moderated
          Jul 12, 2011 3:55 AM (in response to Veeru B)
          unique & duplicate records

          Hi Prabhu,

           

          the mapping looks like below....

           

           

          Regards,

          Veeru

          • Veeru B Master 1,242 posts since
            Sep 27, 2010
            Currently Being Moderated
            Jul 12, 2011 4:57 AM (in response to Veeru B)
            unique & duplicate records

            Hi Prabhu,

             

            Using above mapping if you have source data like below,

             

            ID

            Name

            10

            A

            10

            A

            20

            B

            30

            C

            20

            B

            40

            D

            50

            E

             

             

             

             

             

             

             

             

             

             

            Target data should like below,

             

            Target   1: Valid

            ID

            Name

            10

            A

            20

            B

            30

            C

            40

            M

            50

            E

            Target   1: Invalid

            ID

            Name

            10

            A

            20

            B

             

             

             

             

             

             

             

             

             

             

             

             

             

            Regards,

            Veeru

            • Suresh Soma Newbie 19 posts since
              Jun 18, 2011
              Currently Being Moderated
              Jul 12, 2011 6:20 AM (in response to Veeru B)
              unique & duplicate records

              Hi Prabhu,

               

              For this Scenario We have to use Tranformations

               

              Aggregator----------For Count Duplicate rows and Joiner

               

              SRC------->SQ----------->AGG------>                                                  TGT_UNIQUE

                                                                     JOINER------>RTR--------->

              SRC------->SQ--------------------------->                                                  TGT_DUPLICATE

               

              Let Source to be

              col 
              1,1,2,3,3,4

              IN AGG Take one output Port---Count(col)

              In Joiner Take AGG ports and SQ ports put
              Joiner Condition : source q.col=aggregator.col

              you Get Data at Joiner level

              col count
              1   2
              1   2
              2   1
              3   2
              3   2
              4   1
              IN Router Make 2 Groups

              Unique------Count=1
              Duplicate---------Count>1

              Regards
              Suresh
            • Veeru B Master 1,242 posts since
              Sep 27, 2010
              Currently Being Moderated
              Jul 12, 2011 7:24 AM (in response to Veeru B)
              unique & duplicate records

              Hi Prabhu,

               

              The logic i have provided will load only duplicate records into another target not all records for that id.

               

              Regards,

              Veeru

    • vikas vicky Newbie 1 posts since
      Jan 25, 2013
      Currently Being Moderated
      Feb 28, 2013 8:19 AM (in response to Veeru B)
      unique & duplicate records

      Thanks Veeru! I got the requirement solution

  • Kishu 0016 Novice 33 posts since
    Oct 7, 2010
    Currently Being Moderated
    Jul 13, 2011 11:48 PM (in response to prabhu balakrishnan)
    unique & duplicate records

    Hi Prabhu,

     

    assume source like these.

     

    eid  ename

    1     a

    1     b

    2     c

    3     d

    4     e

    5     f

     

    Now in aggregator t/r take one column i.e count(eid) after in router follow these candition

    1.count(eid)=1

    2.count(eid)>1

     

    take these outputs as unique records and duplicate records in targets.

    • Veeru B Master 1,242 posts since
      Sep 27, 2010
      Currently Being Moderated
      Jul 13, 2011 11:51 PM (in response to Kishu 0016)
      unique & duplicate records

      Hi Kishu,

       

      With this you can load all record for duplicate in one target. like as per above example 1,a and 1,b will go into duplicate records target. is it work if we want to load only duplicate record in to duplicate records target?

       

      Regards,

      Veeru

  • Kishu 0016 Novice 33 posts since
    Oct 7, 2010
    Currently Being Moderated
    Jul 14, 2011 2:44 AM (in response to prabhu balakrishnan)
    unique & duplicate records

    Hi Veeru,

     

    then, we can use single candition i.e,Count(eid)=1(its take unique records) remaining duplicate records we can catch through default group......

    • Deepak N Novice 37 posts since
      May 19, 2011
      Currently Being Moderated
      Jul 14, 2011 10:39 PM (in response to Kishu 0016)
      unique & duplicate records


      Hi All,

       

      I tried all the above suggested ways to find the duplicate records but, I am not successful . Can any one help me in identifying duplicate records,

      My Input:

      Col1 Col2
      1 A
      1 B
      2 C
      2 D
      1 E
      1 F
      3 G
      4 H
      5 I
      6 J
      4 K
      3 L

       

      My Expected output is:

      Col1 Col2 Flag
      1 A 1
      1 B 2
      1 E 2
      1 F 2
      2 C 1
      2 D 2
      3 G 1
      3 L 2
      4 H 1
      4 K 2
      5 I 1
      6 J 1

       

      By Using AG Transformation(Without group by) My Output is,

      Col1-Col2-Flag

      6 J 12

      By Using AG Transformation(With group by on Col1) My Output is,

      Col1-Col2-Flag

      1 F 4

      2 D 2

      3 L 2

      4 K 2

      5 I 1

      6 J 1

      By using Expression trasformation and variables in it my output is:

      Col1 Col2 Flag
      1 A 2
      1 B 2
      1 E 2
      1 F 2
      2 C 2
      2 D 2
      3 G 2
      3 L 2
      4 H 2
      4 K 2
      5 I 2
      6 J 2

       

      Thanks,

      Deepak

       

      
      • Deepak N Novice 37 posts since
        May 19, 2011
        Currently Being Moderated
        Jul 14, 2011 11:05 PM (in response to Deepak N)
        unique & duplicate records

        In expression I have configured ports in the below way,

         

        Col1 (only Input) : Which i am getting from my Sorter

        Out_Col1(Only Output) = Col1

        Out_Flag = iif(col1 = v_col1,2,1)

        v_col1(variable) =  Col1

         

        In the same order I configured ports in my Exp trans.

         

        Please correct me if I am wrong

         

        Thanks,

        Deepak NBK.

        • Veeru B Master 1,242 posts since
          Sep 27, 2010
          Currently Being Moderated
          Jul 14, 2011 11:37 PM (in response to Deepak N)
          unique & duplicate records

          Hi Deepak,

           

          I am not sure how you implemented this. But follow this why may be it will help you.

           

          SRC-->SQ-->SRT-->EXP-->RTR-->TGT

           

          You inout from source is

          Col1 Col2

          1 A

          1 B

          2 C

          2 D

          1 E

          1 F

          3 G

          4 H

          5 I

          6 J

          4 K

          3 L

           

          In sorter sort data by col1 and after sorter data looks like this

           

          Col1 Col2

          1 A

          1 B

          1 E

          1 F

          2 C

          2 D

          3 G

          3 L

          4 K

          4 H

          5 I

          6 J

           

          In exp you have two input ports

           

          in_col1

          in_col2

           

          create variable ports and output ports in the order like below

          v_FLAG= IIF(v_col1=in_col1,2,1)

          v_col1=in_col1

          out_FLAG=v_FLAG

           

          Then out put of expression data looks like this

           

          Col1 Col2 FLAG

          1,A, 1

          1, B, 2

          1, E, 2

          1, F, 2

          2, C, 1

          2, D, 2

          3, G, 1

          3, L, 2

          4, K,1

          4, H, 2

          5, I, 1

          6, J, 1

           

          In router create two groups one for unique records and another one for duplicate records.

          unique=(FLAG=1)

          duplicate=(FLAG=2)

           

          connect two groups to two targets.

           

          let me know still you need how data flow in expression.

           

          Regards,

          Veeru

          • Surya Prakash Reddy Novice 31 posts since
            Aug 24, 2011
            Currently Being Moderated
            Mar 23, 2013 3:42 AM (in response to Veeru B)
            unique & duplicate records

            Hi Veeru ,

            we can achieve this logic using Rank transformatio insted of sorter t/r

            SRC-->SQ-->SRT-->EXP-->RTR-->TGT

             

            SRC-->SQ-->RANK-->EXP-->RTR-->TGT

             

            will  use same logic (take rank column)

             

             

            

            v_FLAG= IIF(v_col1=in_col1,2,1)

            v_col1=in_col1

            out_FLAG=v_FLAG.

             

            could you please currect ,if am wrong.

             

            Regards,

            Surya...

      • Veeru B Master 1,242 posts since
        Sep 27, 2010
        Currently Being Moderated
        Jul 14, 2011 11:38 PM (in response to Deepak N)
        unique & duplicate records

        Hi Deepak,

         

        I am not sure why you used aggregate transformation.

         

        Regards,

        Veeru

        • Deepak N Novice 37 posts since
          May 19, 2011
          Currently Being Moderated
          Jul 15, 2011 2:16 AM (in response to Veeru B)
          unique & duplicate records

          Hi Veeru,

           

          Thanks for your update.  I used aggregator to check suresh's logic.

           

          as per the above logic, When the first record enter into expression it will assign v_col1 to first record col1 value then if condition will always be true right ?

           

          does informatica compiles/executs from first port to last port sequentially?

           

           

          Thanks,

          Deepak

          • Veeru B Master 1,242 posts since
            Sep 27, 2010
            Currently Being Moderated
            Jul 15, 2011 2:31 AM (in response to Deepak N)
            unique & duplicate records

            Hi Deepak,

             

            In informatica  calculation of ports is like below.

             

            Input

            Variable

            output

             

            for the above logic for first record v_FLAG will calculate before assigning new value in v_col1.

             

            let me know if further details required.

             

            Regards,

            Veeru

          • Deepak N Novice 37 posts since
            May 19, 2011
            Currently Being Moderated
            Jul 15, 2011 3:19 AM (in response to Deepak N)
            unique & duplicate records

            Hi All,

             

            I am successful in identifying Duplicate records.

             

            I used Expression Transformation to achieve this as suggested by Veeru. Thanks a lot Veeru.

            Out of this I came to know that there is an order in informatica to evaluate the port values,

             

            The order of evaluation for ports is:

            1. All input ports
            2. Variable ports (in order)
            3. Output ports

             

            Logic which I implemented previously was,

            Col1 (only Input) : Which i am getting from my Sorter

            Out_Col1(Only Output) = Col1

            Out_Flag = iif(col1 = v_col1,2,1)

            v_col1(variable) =  Col1

             

            But it is wrong,

             

            Correct logic is(as suggested by Veeru)

            v_FLAG= IIF(v_col1=in_col1,2,1)

            v_col1=in_col1

            out_FLAG=v_FLAG

             

            we need to write our logic in Variable port(v_Flag) not in output port(Out_Flag) so that when this variable port is evaluated v_col1 value is Null and the logic fails. For the second record v_col1 value will hold previos row's value and my logic will satisfy.

             

            Thanks a lot for all the Informatica Gurus

             

            Thanks,

            Deepak.

  • Manojkumar Venkatachalam Novice 82 posts since
    Dec 18, 2012
    Currently Being Moderated
    Jan 23, 2013 10:12 PM (in response to prabhu balakrishnan)
    unique & duplicate records

    Hi Prabhu ,

     

    Create a expression Transformation and design ports in it as given in the order below.

     

     

    • NO - Input Port containing Number
    • NAME - Input Port containing Name
    • SAL - Input Port containing Salary
    • CONCAT_STR - Variable Port containing the following Expression

     

     

     

    NO || ':' || NAME || ' :' || SAL

          - for concatenating Number , Name and Salary  

    • FLAG - Variable port containing the following Expression

     

     

     

    IIF(CONCAT_STR = PREV_CONCAT_STR, 'DUPLICATE', 'UNIQUE')

       -  to fix the flag as        'UNIQUE' or 'DISTINCT'  

    • PREV_CONCAT_STR - Variable Port containing the value of the Variable Port 'CONCAT_STR'
    • O_FLAG - Outport Port containing the value of Variable Port 'FLAG'

     

    Create a Router transformation and create Two Groups in it with conditions as below :

     

    1. FLAG = 'UNIQUE'  -- > TARGET 1 ( containing all Unique Rows )
    2. FLAG = 'DISTINCT' -- > TARGET 2 ( containing all Distinct rows )
  • vijay bhaskar Newbie 6 posts since
    Jul 2, 2012
    Currently Being Moderated
    Mar 1, 2013 12:29 AM (in response to prabhu balakrishnan)
    unique & duplicate records

    In the below link check question number 5. In that question check subquestion Q2 for the sollution.

     

    Informatica scenario questions

  • Viral Raichura InfaEmp 134 posts since
    Sep 7, 2010
    Currently Being Moderated
    Mar 4, 2013 7:58 PM (in response to prabhu balakrishnan)
    unique & duplicate records

    Hi,

     

     

     

    You will find following example on Informatica Marketplace Website useful.

     

     

     

    You will also find over 100 mapping samples and many other useful tools on the same website.

     

     

     

    https://community.informatica.com/solutions/mapping_remove_duplicates

     

     

     

    https://community.informatica.com/solutions/powercenter_filter_duplicates

     

     

     

    https://community.informatica.com/solutions/remove_duplicate_records_sql_transformation

     

     

     

    regards,

     

    ~ VR

  • Viral Raichura InfaEmp 134 posts since
    Sep 7, 2010
    Currently Being Moderated
    Mar 27, 2013 6:06 AM (in response to prabhu balakrishnan)
    unique & duplicate records

    Hi,

     

    You may find following example on Informatica Marketplace Website useful.

     

    You will also find over 100 mapping samples and many other useful tools on the same website.

     

    https://community.informatica.com/solutions/powercenter_filter_duplicates

     

    regards,

    ~ VR

More Like This

  • Retrieving data ...

Bookmarked By (0)