top button
Flag Notify
Site Registration

Create Rows depending on count in Informatica

+1 vote
488 views

I have input data in a flat file, for which I want the specific output file.

    data.csv contains
    A,2
    B,3
    C,2
    D,1

And required output should be

    A
    A
    B
    B
    B
    C
    C
    D

Here I need to create output rows depending upon value in column. I tried it using java transformation and I got the result. Do we have any other way to do it?

posted May 27, 2014 by Rohini Agarwal

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

1 Answer

+1 vote

If you need an alternative implementation, you can use a helper table and a Joiner transformation.

1.Create a helper table and populate it with appropriate amount of rows (you need to know the maximum value that may appear in the input file). There is one row with COUNTER=1, two rows with COUNTER=2, three rows with COUNTER=3, etc.

2.Use a Joiner transformation to join data from the input file and the helper table - since the latter contains multiple rows for a single COUNTER value, the input rows will be multiplied.

COUNTER
-------------
1
2
2
3
3
3
4
4
4
4
...

Depending on your RDBMS, you may be able to produce the contents of the helper table using a SQL query in a source qualifier.

answer May 28, 2014 by Shweta Singh
Similar Questions
0 votes

can anyone tell me how to create mapping for the following scenario:

Source:                 Target:
emp_id  org             EMP_COUNT  ORG
1         x                2         X
2         Y                2         Y
3         X                1         z
4         Y
5         Z
0 votes

I am using aggregator transformation in my Informatica mapping where I am counting number of records coming from sql override.I need to pass zero value to target table as counts in case if I get 0 number of return counts from count(*) function of Aggregator Transformation.

0 votes

My PreSQL query in the WorkFlow of Informatica has 2 parts:

Insert query
Refresh Stats procedure call
When no semicolon ; is present between these 2 parts, the record count is given in Millions. When a semicolon ';' is present the record count is given in Thousands.

Two Questions:

Why is it not throwing an Error for the absence of a semicolon?
Why the increased record count?

0 votes

My source data is :

ID TimingID Timingtype
1 100 supplier
1 102 dealer
1 103 dealer
2 200 xyz
2 205 abc

I want my target to be like this :
ID T1_TimingID T1_Timingtype T2_TimingID T2_Timingtype T3_TimingID T23_Timingtype
1 100 supplier 102 dealer 103 dealer
2 200 xyz 205 abc null null

Kindly suggest.

Regards
Sandeep Nanda

+2 votes

In my mapping it tooks long time to fetch rows to the target table but it reads those rows from .CSV file within a minute.In this mapping One source is a flat file another source is a table which is having data from target.Here we create a logic to UPDATE or DELETE or INSERT in target based on comparison on the data from flat file and the source table(data from target table).while seeing sesion log it reads data from flat file within a minute but it fetches those data to the target to taget 9 rows/sec throu enter image description hereghput.Here the target table is created using unix script ie CREATE TABLE STG_LM_INSTITUTION as (SELECT * FROM LM_INSTITUTION);Target in this mapping is STG_LM_INSTITION.this is the copy of final target(LM_INSTITUTION).I think the problem is due to creation of the taget table in script but i'm not sure.Anyone please help me to solve this issue.Source flat file has 2L rows. I run this mapping with 2L rows.After 11hrs it fetches only 1L records into the target.But while running using 500rws only it fetches records in one minute to the target .

...