Thursday, August 23, 2012

Get top 5 records to target without using rank Get top 5 records to target without using rank

Get top 5 records to target without using rank

by on in
How to get top 5 records to target without using rank ?

Solution:
  1. Drag the source to mapping and connect it to sorter transformation.
source sorter mapping 
Arrange the salary in descending order in sorter as follows and send the record to expression.



  1. Add the next value of sequence generator to expression.(start the value from 1 in sequence generator).


Connect the expression transformation to a filter or router. In the property set the condition as follows



  1. Finally connect to the target.
By at
Labels:

10 comments:

  1. UnknownMarch 12, 2013 at 11:36 PM

    Hi kareem, I am new to informatica. I tried above mapping but the data is not loaded into target but session n workflow is successful, could you please reply me.
    Thanks,
    Anusha

    ReplyDelete
    Replies
    1. KareemMarch 13, 2013 at 8:57 AM

      Hi Anusha,
      1. First drag the ports from SQ to Sorter T/R.
      2. here Sorter t/r, Sort the data based on Sal in Descending Order.
      3. Crete a Sequence Transformation, Connect NEXTVAL port to Expression T/R(Create )
      4. Create a Filter t/R and give the filter condition as NEXTVAL<=5
      5. Connect the ports to the target.
      Diffidently you will get top5 records from source.

      Check the target table what u define in mapping designer.

      Thanks
      kareem

      Delete
  2. UnknownMarch 12, 2013 at 11:37 PM

    This comment has been removed by the author.

    ReplyDelete
  3. UnknownMarch 15, 2013 at 8:19 AM

    Hi kareem your is too good and so informative. I have one question faced in interview could you please answer it

    How to remove duplicates in flatfiles with out using sorter transformation.

    waiting for ur reply

    Thanks in advance

    ReplyDelete
    Replies
    1. KareemMarch 15, 2013 at 1:28 PM

      Hi Anuradha,
      check the fallowing ways to removing duplicates

      1. Using sorter( Check DISTINCT option)

      2. Aggrigator (Group by)

      3.Rank (Group by)

      4.Expression (By using variable port)

      5. Dynamic Lookup cache

      6.Using Source Qualifier (DISTINCT)

      Note: In case Source is a file Distinct property cant work it is disabled.

      Thanks
      kareem

      Delete
  4. UnknownNovember 12, 2013 at 12:57 PM

    Hi Kareem,

    This is Haritha. I have one question faced in interview. could you please answer it

    How to load Max 2 salaries based on dept wise in Informatica?

    ReplyDelete
  5. UnknownOctober 26, 2015 at 11:46 AM

    This article is very nice and informative to learners
    Informatica training, informatica training in bangalore, informatica online training

    ReplyDelete
  6. AnonymousOctober 28, 2015 at 11:53 PM

    Hi Kareem.
    This is shobha.my doubt is if a source has n records,then how to load n+5 records into target without using SQL override.
    Waiting for it reply.

    ReplyDelete
  7. AnonymousOctober 28, 2015 at 11:54 PM

    Hi Kareem.
    This is shobha.my doubt is if a source has n records,then how to load n+5 records into target without using SQL override.
    Waiting for it reply.

    ReplyDelete
  8. AnonymousOctober 28, 2015 at 11:54 PM

    Hi Kareem.
    This is shobha.my doubt is if a source has n records,then how to load n+5 records into target without using SQL override.
    Waiting for it reply.

    ReplyDelete

Thank you :
- kareem

Subscribe to: Post Comments (Atom)