top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

How to convert multiple rows into single row in iformatica for large volume of data, need best solution

+1 vote
658 views

I have data in table A as below

Assetid   attribute   value
    1546    Ins_date   05062011
    1546    status     active
    1546    X          10.4567
    1546    Y          27.56
    1546    size       17
    675     X          4.778
    675     Y          53.676
    675     depth      5
    675     st_date    06092010

I have data as above in table A. This table has many Assetids 1546,675,....etc. attributes might vary for assets.

I want output as below:

assetid  ins_date  status  X        Y       Size  depth  st_date
1546     05062011  active  10.4567  27.56   17    null   null
675      null      null    4.778    53.676  null  5      06092010

I have created Stored procedure, then called in Informatica to achieve this output. However, since i have large volume of data, it is taking much time to load.

Please suggest me other easy and best way to load it.

posted Mar 28, 2014 by Pooja Bhanout

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

Use a router to split the rows into separate groups depending on attribute and then use a set of joiners to merge the rows with the same assetid values.

I would recommend to use a dynamic lookup on the target table to determine if a record with a given name already exists - if it does, you should update the first empty address columns with a new address; otherwise, insert a new record

Use an Aggregator transformation to condense the records into one record per assetid. Then for each attribute, create a port that returns MAX(value) where the attribute matches. Note that this method assumes that you know all possible attributes ahead of time.

answer Apr 2, 2014 by Shweta Singh
Similar Questions
+1 vote

Suppose we have a table with the following data:

ClientID    ClientName
3           Sowrabh Malhotra
4           Saji Mon
6           Sajith Kumar
7           Vipin Job
8           Monoj Kumar

We need to concatenate the ClientName column like the following:

Sowrabh Malhotra, Saji Mon, Sajith Kumar, Vipin Job, Monoj Kumar
+1 vote

I have a scenario wherein the data is present in just single row. with multiple columns. The number of columns may extend if new record is inserted.

name city name city name city name city......
N1 C1 N2 C2 N3 C3 N4 C4.....

i want to write the output of this data to a fixed width flat file through informatica like below:

N1 C1
N2 C2
N3 C3
N4 C4

Please suggest..

+3 votes

Say I want to convert email into the structured data so that it can be stored into Database, any clue how to achieve this?

...