Search code examples
informaticainformatica-powercenter

I want to store a column value of the first record and compare that value with the rest of the records

For instance, I have below data

col1|col2|col3|col4

aa|123|456|50

bb|101|222|50

cc|102|232|70

dd|103|142|70

ee|104|472|50

Here I want col4 value of the first record should be taken and compared with the rest of the records col4 and if it is mismatching, I want to capture it in another port. I would like to do it using Informatica not using Shell.

Desired Output

col1|col2|col3|col4|col5

aa|123|456|50|Matching

bb|101|222|50|Matching

cc|102|232|70|Not matching

dd|103|142|70|Not matching

ee|104|472|50|Matching

Please help me achieve this using Informatica not using Shell.

Solution

  • Ok, there you go. Create an expression and within:

    1. Create a variable port that will just increase with every row, e.g. v_counter = v_counter + 1. Put it as the very last on the list of ports.
    2. Create a variable port to hold your reference value, e.g. v_ref_value = IIF(v_counter = 1, col4, v_ref_value). Make sure the v_counter is still the very last port on the list.
    3. Add col5 output port defined as IIF(col4=v_ref_value, 'Matching', 'Not matching')