top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Informatica: Prev column value display

0 votes
221 views

The data in the source table is as follows: -

Empno   Ename  sal
101     Allen  1000
102     Alex   2000
103     Tom    1500
104     Cb     2100

I want the output to be as follows :-

Empno   Ename  sal
101     Allen  0
102     Alex   1000
103     tom    2000
104     Cb     1500

Can you please tell me by using which transformation i can acheive the result. Also plz let me know the logic

posted Sep 22, 2014 by Sunil

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

1 Answer

0 votes

Add an expression transformation with two variable ports:
PORT EXPRESSION
in_sal N/A
v_previous_sal v_current_sal
v_current_sal in_sal
out_sal v_previous_sal

Read this excerpt from the documentation to understand the port order:

The Integration Service evaluates ports in the following order:
1. Input ports. The Integration Service evaluates all input ports first since they do not depend on any other ports. Therefore, you can create input ports in any order. Since they do not reference other ports, the Integration Service does not order input ports.

  1. Variable ports. Variable ports can reference input ports and variable ports, but not output ports. Because variable ports can reference input ports, the Integration Service evaluates variable ports after input ports. Likewise, since variables can reference other variables, the display order for variable ports is the same as the order in which the Integration Service evaluates each variable.

For example, if you calculate the original value of a building and then adjust for depreciation, you might create the original value calculation as a variable port. This variable port needs to appear before the port that adjusts for depreciation.

  1. Output ports. Because output ports can reference input ports and variable ports, the Integration Service evaluates output ports last. The display order for output ports does not matter since output ports cannot reference other output ports. Be sure output ports display at the bottom of the list of ports.
answer Sep 23, 2014 by Shweta Singh
Similar Questions
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....

0 votes

I have data for current year as well as next year in a table.

Sample Table data:

Year,Period,prd_Desc,Amount
2014,11,    Humira,  120
2015,11,    humira,  140
Key Coulmn are Year,Period,prd_Desc

If the data present for next year and for same period, i need that value in a separate column. Like below

Year,Period,prd_Desc,Amount_curnt_yr,Amount_next_yr
2014,11,    humira,  120,            140

I can achive this by doint a left outer join between same table using below query:

select a.Year,a.Period,a.prd_Desc,a.Amount as Amount_curnt_yr,b.Amount as Amount_next_yr 
from (select Year,Period,prd_Desc,Amount 
      from tableA) a 

left outer join (select Year,Period,prd_Desc,Amount from tableA) b on
b.year=a.year+1 and a.Period=b.period and a.prd_Desc=b.prd_Desc
I was trying to get it in a simngle query without using left outer join, but could not. If anybody can share any idea, that would helps

+1 vote

Hi,
I have a scenario, where in we have a single record and multiple columns like this

Record numer cd1 cd2 cd3 cd4 cd5 cd6 cd7 cd8 cd9
123 12 null 13 14 null 15 16 17 null
Here we have value for cd1 and not for cd2 and we have value for cd3 so cd2 is empty so cd3 should get into cd2 since it was empty so we should move the next available values to previous available spaces.

Does anyone know how to achieve this scenario?

+1 vote

I have a Column (SALARY) in Source Table from Relational DB, for example 15000 is a record in SALARY column and I want to format it as $15,000.00 into the Target table which is a Relational DB using Expression Transformation.

Thankyou

+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.

...