CODEWITHSUNDEEP
informaticainformatica-powercenter
sha12
sha12

Reputation: 11

in informatica - loop through based on different condition

in continuation to the question posted loop through based on different condition in informatica

i am trying to find a way to loop through the set of entries in informatica , if some condition is not met. Below are the columns in each of the flat files

FF1 -> ID

FF2 -> ID, type, SecurityID

For the matching ID in FF1 and FF2 - it checks for record in FF2 which has value , type = P and for that corresponding record , lookup is done for SecurityID column with tbl1. if the match is not found in tbl1, it has to loop again for the same ID found in both flat files (FF1 and FF2) but now with type = C, lookup is done for SecurityID column with tbl1 this time , now if the match is not found again...Null would get updated in the target table.

If the type is other than P and C, there is no loop as such it verifies if data in SecurityID column is found in tbl1. if found , it updates target table, else value will be null in target table.

FF1

ID
===
1
2
3

FF2

ID |type |SecurityID
===================
1  |C    |S1
1  |P    |S2
1  |D    |S2
2  |B    |S3
2  |C    |S1
3  |P    |S2
3  |C    |S3

TBL1

SECURITYID |VAL
===============
S1         |10

OUTPUT IN TARGET TABLE SHOULD BE

TGT TABLE

ID |TYPE |VAL
============
1  |C    |10
1  |D    |Null
2  |B    |Null
2  |C    |10
3  |C    |Null

Upvotes: 0

Views: 112

Answers (1)

Zero
Zero

Reputation: 1847

This can be done using joins.

You need to take FF1, FF2, and TBL1, and perform the following join

FF1 INNER JOIN FF2 (since you're checking for matching ids only in both files), then again join with TBL1 as the left join since you're only doing a lookup in that table.

Your final join would look like

(FF1 INNER JOIN FF2) LEFT JOIN TBL1

Upon this, you'll need to remove unwanted columns from the mapping and map needed columns to your target.

Just before your final target, you'll need to add a filter to filter out Ps and Cs, if they had NULL in the val column, i.e. they were not found in the lookup.

Upvotes: 0

Related Questions