Informatica Scenario: How to convert concatenated columns to rows?


SRC:

 we have source data as below, we have multiple products under one category and products are delimited by comma (,). 


TGT:
we need to show output as below using Informatica PowerCenter.




Solution: 

 Assumption: we can have maximum of 4 products under one category. 

 Below is the data flow 


Used reg extract to divide product names into individual product names in 1st expression transformation.
Below are the expressions that I have used:
P1 to REG_EXTRACT(PRODUCTS,'((\w|\s)+),*((\w|\s)*),*((\w|\s)*),*((\w|\s)*),*((\w|\s)*)',1)
P2 to REG_EXTRACT(PRODUCTS,'((\w|\s)+),*((\w|\s)*),*((\w|\s)*),*((\w|\s)*),*((\w|\s)*)',3)
P3 to REG_EXTRACT(PRODUCTS,'((\w|\s)+),*((\w|\s)*),*((\w|\s)*),*((\w|\s)*),*((\w|\s)*)',5)
P4 to REG_EXTRACT(PRODUCTS,'((\w|\s)+),*((\w|\s)*),*((\w|\s)*),*((\w|\s)*),*((\w|\s)*)',7)


Use normalizer transformation to convert these columns to rows and use filter transformation to filter out null products. (Filter condition: PRODUCTS<>'')

 It worked for me, let me know if you face any issue.

Comments

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