Informatica Scenario: How to get salary and commission as column values when they both mentioned in same column data in source with type?


SRC:

 Source data looks like below. Here Type=S means salary and Type=C means commission. 

Target:

 we need salary and commission should come as columns against empid. 

Mapping Flow:
Below is the SQL logic:

 SELECT S.EMPID, S.AMT AS SAL, C.AMT AS COM
  FROM (SELECT *
          FROM test_emp_sal_com
         WHERE TYPE = 'S') S
       LEFT OUTER JOIN (SELECT *
                          FROM test_emp_sal_com
                         WHERE TYPE = 'C') C
          ON C.EMPID = S.EMPID

 same logic has been implemented using Informatica and the below is the mapping data flow.


same logic can also be used by using lookup on source. 

Comments

  1. UnknownJune 20, 2015 at 4:47 PM

    It is the best blog with images&logic. For any other information and tutorials can visit .....

    Check this site Tekslate for indepth informatica-training
    Go here if you’re looking for information on informatica-training

    ReplyDelete
  2. UnknownOctober 29, 2015 at 9:18 AM

    This is very nice explanation
    INFORMATICA TRAINING IN BANGALORE, INFROMATICA ONLINE TRAINING, INFORMATICA, INFROMATICA DATAQUALITY TRAINING, BEST INFORMATICA TRAINING IN BANGALORE

    ReplyDelete
  3. James ZicrovSeptember 5, 2020 at 10:40 AM

    I feel Informatica and other aspects and products really help one provide solutions about more tools and solutions.

    Informatica Read JSON

    ReplyDelete

Post a Comment

Popular posts from this blog

Informatica Repository Queries to get connection names, command tasks and event wait file names

Query1:  Query to get folder name, workflow name, session names and all the connections used in sessions. SELECT WF.SUBJECT_AREA AS FOLDER_NAME, WF.WORKFLOW_NAME AS WORKFLOW_NAME,        T.INSTANCE_NAME AS SESSION_NAME, T.TASK_TYPE_NAME,        C.CNX_NAME AS CONNECTION_NAME, V.CONNECTION_SUBTYPE, V.HOST_NAME,        V.USER_NAME, C.INSTANCE_NAME, C.READER_WRITER_TYPE,        C.SESS_EXTN_OBJECT_TYPE   FROM REP_TASK_INST T,        REP_SESS_WIDGET_CNXS C,        REP_WORKFLOWS WF,        V_IME_CONNECTION V  WHERE T.TASK_ID = C.SESSION_ID    AND WF.WORKFLOW_ID = T.WORKFLOW_ID    AND C.CNX_NAME = V.CONNECTION_NAME    AND WF.SUBJECT_AREA = <FOLDER NAME>      Note: If SESS_EXTN_OBJECT_TYPE=78 then its reader, If 79 then its writer    Query2:     ...
Read more

SQL Test : Sample Questions from Amazon Test (1/2)

Some companies are conducting SQL tests. Below are some sample questions from SQL test. These questions are asked in Amazon SQL test. Question 1 / 10 Order Sample Data: ORDER_DAY          ORDER_ID             PRODUCT_ID    QUANTITY      PRICE ------------------ -------------------- ---------- ---------- ---------- 01-JUL-11          O1                   P1                  5           5 01-JUL-11          O2                   P2                  2          10 01-JUL-11          O3                   P3                 10          25 01-JUL-11          O4                   P1                 20           5 02-JUL-11          O5                   P3                  5          25 02-JUL-11          O6                   P4                  6          20 02-JUL-11          O7                   P1                  2           5 02-JUL-11          O8                   P5                  1          50 02-JUL-11          O9                   P6    ...
Read more

SQL Test : Sample Questions from Amazon Test (2/2)

  SQL questions are about monorail company. Database consists of tables for a monorail company.   Below listed table definition with column name and one row of sample data. 1.       Table employee contains information of all the employee in the organization (including drivers of monorail).   | eid   | ename    | salary | | 555   | Raheem   | 235     |   2. Table monorail Contains the information aboubt each monorail.   | rid     | rname        | running_range | | 1234   | Speedster   | 454             |   3. Table route_info contains the description of route on which monorails used to travel.     | route_no     | origin        | distance | cost | | 4456        | Gwalior      | Delhi     | 543   |   4. Table assignment contains the data about which monorail is driven by which employee. This can be a many-to-many relation.   | eid     | rid        | | 101  ...
Read more