top button
Flag Notify
Site Registration

informatica show the latest Status based on 2 attributes

0 votes
179 views

I need to show the latest Status based on 2 attributes (LAST_UPDATE and STAUS)
How can I do it in informatica? the source is flat file

Example:

NUMBER --------------------LAST_UPDATE ----------------- STATUS  
-----1 -----------------------01/26/2015  ----------------------   CREATED                  
-----1 ----------------------   01/27/2015  ------------------UNDER_PROCCESS                           
-----1----------------------    01/28/2015  ---------------------COMPLETED  
 -----2---------------------- 01/28/2015 ------------------ CREATED  
-----3---------------------- 01/28/2015 --------------------- UNDER_PROCCESS

Result should be

NUMBER --------------------LAST_UPDATE ------------- STATUS  ---------------LAST_STAUS  
-----1 -----------------------01/26/2015  ----------------------   CREATED -----------COMPLETED                 
-----1 ----------------------   01/27/2015  -----------------UNDER_PROCCESS ---- COMPLETED                           
-----1----------------------    01/28/2015  ---------------------COMPLETED ----------COMPLETED  
 -----2---------------------- 01/28/2015 ------------------ CREATED  ---------------- CREATED  
-----3---------------------- 01/28/2015 -------------UNDER_PROCCESS --UNDER_PROCCESS   
posted Feb 9, 2015 by Amit Sharma

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

1 Answer

0 votes

You can either use an Aggregator transformation or do it in an Expression transformation using variable ports.

Using Aggregator
1.In a sorter transformation, sort on NUMBER and LAST_UPDATE, in ascending order
2.In aggregator group by on NUMBER. Optionally use the LAST function to get the latest status. By default Aggregator will output the value for last row for STATUS.
3.
Use a joiner to join the output of Aggregator and Sorter.
SQ ----> Sorter -----> Agg----> Joiner ----> Target
|_____________________^

Using Expression
1.Sort the data on NUMBER (ascending) and LAST_UPDATE (descending)
2.
In expression transformation:

NUMBER (i/o)
LAST_UPDATE (i/o)
STATUS (i/o)
v_LAST_STATUS (v) = IIF(STATUS<>v_PREV_STATUS, STATUS,v_PREV_STATUS)
LAST_STATUS (o) = v_LAST_STATUS
v_PREV_STATUS (v) = STATUS
Make sure the order of ports is correct.
answer Feb 11, 2015 by Shweta Singh
Similar Questions
0 votes

Hi All,

I want to implement the below scenaio without using pl/sql procedure or unix script. I want to be implement by using Pwoercenter only.

Kinly suggest me.

1.I have a table called emp_details with coulmns (empno,ename,salary,emp_status,flag,date1) .

2.if someones updates the columns emp_status='abc' and flag='y' ,Informatica WF 1 would be in contunous running status and checking emp_status value "ABC"

3.If it found record / records then query all the records and it will invoke WF 2.

  1. WF 1 will pass value ename,salary,Date1 to WF 2 (Wf2 will populate will insert the records into the table emp_details2).

Kindly tell me the informatica approach instead of plsql and shell script...

Thank you

+1 vote

I want to implement the below scenario without using pl/sql procedure or unix script. I want to be implement by using Powercenter only.

Kindly suggest me.

  1. I have a table called emp_details with columns (empno,ename,salary,emp_status,flag,date1) .
  2. If someones updates the columns emp_status='abc' and flag='y' , Informatica WF 1 would be in continuous running status and checking emp_status value "ABC"
  3. If it found record / records then query all the records and it will invoke WF 2.
  4. WF 1 will pass value ename,salary,Date1 to WF 2 (Wf2 will populate will insert the records into the table emp_details2).

    Kindly tell me the informatica approach instead of plsql and shell script...

+1 vote

In Informatica's PowerCenter, is it possible to set a parameter's value based on the output of a stored procedure? The parameter I want to set is a parameter I defined in a flat-file data object.

0 votes

I have a directory of files where each file is in one of three file formats. We can use the filename to determine the file format for mapping data. I would like to loop through all the files in the directory and based on the filename direct it towards the associated session mapping, delete the file on success, update the control table and then move to the next file. I'd like the execution to be deterministic. What are some of my options?

+1 vote

My source data

id    |   value
10       a,b,c
20       d,e,f

I want output is

id    |   value
10       a
10       b
10       c
20       d
20       e
20       f

How to do this scenario

...