Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 31
Like Tree1Likes

Thread: Informatica Real time scenarios

  1. #11
    Senior Member vijay's Avatar
    Join Date
    May 2012
    Location
    Hyderabad
    Posts
    515
    Q11) How to implement the functionality of NOT IN or NOT EXISTS in informatica.

  2. #12
    Member
    Join Date
    May 2012
    Posts
    93
    Let me frame the question Q11 as below:
    - "SELECT * FROM A WHERE A.column(s) NOT IN (SELECT column(s) FROM B)
    - "SELECT * FROM A WHERE NOT EXISTS (SELECT 1 FROM B WHERE A.column(s)=B.column(s))

    To achieve the above, I think we can implement "A LEFT OUTER JOIN" using JOINER transformation and then filter the rows based on below condition to achieve what you are looking for.

    Condition in FILTER transformation: [B.column values] is NULL
    Last edited by krkanth; 06-06-2012 at 07:06 AM.

  3. #13
    Junior Member
    Join Date
    May 2012
    Posts
    8
    Answer for Q2:
    we can achieve this by using JAVA transformation in INFORMATICA.
    Procedure:
    1)drag source
    2)create JAVA transformation, select Active since no of records is going to change
    3)drag the port from Source Qualifier into JAVA transformation, uncheck output port option
    4)create another output port
    5)In JAVA code option write the belwo code

    For (i=1,i<=inputport,i++)
    {
    outputport=i;
    generateRow();
    }
    connect output port to target

  4. #14
    Junior Member
    Join Date
    May 2012
    Posts
    8
    Answer for Q3:

    For (i=inputport,i>=2000,i--)
    {
    outputport=i;
    generateRow();
    }

  5. #15
    Junior Member
    Join Date
    May 2012
    Posts
    8
    Q9 Answer:

    we can achieve this by two ways 1)using normalizer transformation by setting level and occurence
    2) by using combination of expression and union transformation

    Q10 Answer:

    we can achieve this by using IS_NUMBER() function

  6. #16
    Senior Member vijay's Avatar
    Join Date
    May 2012
    Location
    Hyderabad
    Posts
    515
    Answer for Q4:

    Go to the source analyzer. Edit the flat file source deifnition, go to the properties tab and check the option "Add Currently Processed Flat File Name Port". This will add "CurrentlyProcessedFileName" port to the definition. Now connect this port to the target table.

  7. #17
    Senior Member vijay's Avatar
    Join Date
    May 2012
    Location
    Hyderabad
    Posts
    515
    Solution to question1

    Creat the target flat file defintion inthe target designer. Go to the ports tab and add the FileName port by clicking on the small square box on the right-corner.

    Follow the below steps for creating the mapping:

    Connect source qualifier to sorter
    Sort the data on catergory id
    connect sorter to expression transforamtion. Create the below ports in the expression transformation.

    Code:
    v_curr_cat_id  (variable port)= category_id
    o_flag (output flag) = IIF(v_curr_cat_id = v_prev_cat_id, 1,0)
    v_prev_cat_id  (variable port)= category_id
    Now connect expression transformation to transaction control transformation. In the transaction control, specify the below condition:

    Code:
    IIF(o_flag=1, TC_COMMIT_BEFORE, TC_CONTINUE_TRANCATION)
    Connect the category port of transaction contorl to FileName port of target definition.

  8. #18
    Senior Member
    Join Date
    Aug 2012
    Posts
    595
    Hi,

    Could someone please answer the questions "Q5) Source contains the list of file names with a complete directory structure. Sample data is shown below." ?

    I need answer for this ASAP.

    Thanks!!

  9. #19
    Junior Member
    Join Date
    Aug 2012
    Location
    A, A
    Posts
    3

    Answer

    Quote Originally Posted by vijay View Post
    Q6) The source data is shown below:

    Code:
    Ename, Deptcount
    ------------------
    Mark,   3
    Henry   2
    Chris    1
    In the target table, i want the output according the dept count. I want to repeat Mark 3 times, Henry 2 times and Chris 1 time in the target. The data in the target should look as

    Code:
    Mark,   3
    Mark,   3
    Mark,   3
    Henry   2
    Henry   2
    Chris    1
    Create a mapping to solve this informatica problem.

    Answer:
    Flow : SQ > Normalizer > Filter > Target

    Normalizer Transformation : Normalize the C1, C2 columns and set the occurrence to 3.

    c1 c2 GK_Grp GCID_Grp
    a 2 1 1
    a 2 2 2
    a 2 3 3
    b 3 4 1
    b 3 5 2
    b 3 6 3


    Filter Transformation : paas col C1,C2, GCID_Grp to Filter

    Filter Cond : (C2<=GCID_Grp)


    c1 c2
    a 2
    a 2
    b 3
    b 3
    b 3

    Enjoy !!

  10. #20
    Senior Member vijay's Avatar
    Join Date
    May 2012
    Location
    Hyderabad
    Posts
    515
    Hi himanshu,

    What if i dont know the value of c2 ahead. Here you have set the occurrence as 3. If in the next run if i get c2 as 10, then this wont work.

Page 2 of 4 FirstFirst 1234 LastLast

Similar Threads

  1. MongoDB Vs Oracle for Real time search
    By Oracleguy in forum Oracle
    Replies: 0
    Last Post: 12-11-2012, 10:23 PM
  2. Soak Testing in real time
    By Manoj Tiwari in forum Testing
    Replies: 1
    Last Post: 10-04-2012, 07:16 AM
  3. Real time processing in informatica
    By akhil in forum Data Warehouse
    Replies: 0
    Last Post: 08-01-2012, 12:20 PM
  4. Replies: 1
    Last Post: 07-03-2012, 01:21 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •