INSERT 3 flag in a mapping in relation to a field in a table

Question

i have this scenario in power center designer:

I have a first table COSTO_PDR with primary key:

ID_PDR and YEARS
I have a second table GAS_COSTO_PDR_PREMI_STOR with primary key:

ID_PDR,YEARS and ID_TIPOLOGIA_PREMIO.
ID_TIPOLOGIA_PREMIO has only one of this 3 values: A,B and C.

I want on my target TARGET_COSTO_PDR: this field: ID_PDR YEARS A B C

where A, B and C are boolean field relating to the values of ID_TIPOLOGIA_PREMIO of GAS_COSTO_PDR_PREMI_STOR..

I do a LOOKUP TRASFORMATION for to take ID_TIPOLOGIA_PREMIO but i have only a value.

for example:

COSTO_PDR
---------------
ID_PDR | YEARS|
---------------
1111111  2014

GAS_COSTO_PDR_PREMI_STOR
---------------------------------------
ID_PDR  |  YEARS  |ID_TIPOLOGIA_PREMIO|
---------------------------------------
1111111  2014       A
1111111  2014       B
1111111  2014       C

TARGET_COSTO_PDR
------------------------------------
ID_PDR |  YEARS  |  A  |  B  |  C  |
------------------------------------
1111111  2014      Y      Y    Y

How i can do for to have this target in POWERCENTER DESIGNER???? thanks for help

Answer 1

Create an unconnected lookup on GAS_COSTO_PDR_PREMI_STOR with the conditions on all the three fields

ID_PDR = inp_ID_PDR
YEARS = inp_YEARS
ID_TIPOLOGIA_PREMIO = inp_ID_TIPOLOGIA_PREMIO

Return any one of the fields from the lookup

Now in an expression transformation create 3 output ports corresponding to your 3 target fields A, B and C with the following expressions:

out_A: IIF(ISNULL(LKP.lkp_GAS_COSTO_PDR_PREMI_STOR(src_ID_PDR, src_YEARS, 'A')),'N','Y')

out_B: IIF(ISNULL(LKP.lkp_GAS_COSTO_PDR_PREMI_STOR(src_ID_PDR, src_YEARS, 'B')),'N','Y')

out_C: IIF(ISNULL(LKP.lkp_GAS_COSTO_PDR_PREMI_STOR(src_ID_PDR, src_YEARS, 'C')),'N','Y')