top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Get max value of a column in my source table in Informatica

0 votes

I have the following table with the shown data in it:

send_date | household_ID

11-20-2014 | 123
11-20-2014 | 456
11-15-2014 | 789

I need to do 2 things:

1) Calculate the max value for send_date

2) Filter out any rows whose send_date does not match that value

In other words, I want the output to be:

send_date | household_ID

11-20-2014 | 123
11-20-2014 | 456

Row number 3 should be filtered out as its send_date is not the max.

I tried creating an aggregate, grouping by all columns, and creating a new output port called MAX_DATE with an expression of MAX(SEND_DATE), then have a filter transformation with the condition MAX_DATE = SEND_DATE

This lets all rows through, though. What can I do to make this work....

posted Jan 2, 2015 by Sachin

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

1 Answer

0 votes

•Use two source qualifiers for the same source table or - better yet - stay with a single SQ but split data into two pipelines (see the comment below for details).
•Calculate MAX_DATE value using the first one and an aggregator.
•Then join data from the second source qualifier on the SEND_DATE column.

answer Jan 6, 2015 by Shweta Singh
Similar Questions
+1 vote

In my source table data is

s1 ,   p1,     10
s1 ,   p2,     xyz
s1 ,   p3 ,    abc
s2 ,   p1 ,    20
s2 ,   p2 ,    xyz
s2 ,   p3 ,    abc

I want two target tables, first table is based on s_name s1, second table based on s_name s2. Both table contains contains p_name and value.

The target table data like as

Table s1

p1,    10
p2,    xyz
p3,    abc 

Table s2

p1,    20
p2,    xyz
p3,    abc 
+2 votes

Currently, we have a lot of ETLs developed and the underlying table and column names are going to change. For example, the physical names used abbreviated names rather than full names since we wanted to deploy on Oracle, but for now, we decided we will use only SQL Server, hence there is a discussion on using full names at the DB level. This will impact the ETLs that we have already developed.

I was just wondering whether there is any efficient way to remap the ETL to the changed column names? I can provide a file with old and new table/column names as input.

Any help on this will be really appreciated.

+2 votes

My question is very similar to the one below, but on a informatica environment:

Retrieving the index of an inserted row

Here is a brief summary of the issue: I'm trying to figure out how I can insert a row into a table and then find out what the value of the auto_incremented id column was set to so that I can insert additional data into another table. Our target is SQL server 2008. We have a table which has to be populated by informatica ETLs and the application is also using the same table - so, we can't use informatica sequence generator.

In the past when I have used Oracle database, there was a Oracle sequence generator transformation available in Informatica - but for SQL server, I am not sure.

Any solutions please?

0 votes

Using Informatica designer, is there a way to run a complex SELECT statement as-is against a source database, and workflow it into a target table? For example, SQL Server Integration Services makes it really easy to create source/target connections, paste your source SQL, and map the results to the target table. When the package is run, SQL runs against the source, and results are dumped into the target.

Is something like that available in Informatica?