top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

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

0 votes
166 views

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

posted May 15, 2014 by Pooja Bhanout

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

1 Answer

0 votes

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')
answer May 19, 2014 by Shweta Singh
Similar Questions
0 votes

Suppose if we have duplicate records in a table temp_n. Now I want to pass unique values to t1 and duplicate values to t2 in single mapping? How can we achieve?

+2 votes

My question is very similar to the one below, but on a informatica environment:

Retrieving the index of an inserted row

Here is a brief summary of the issue: I'm trying to figure out how I can insert a row into a table and then find out what the value of the auto_incremented id column was set to so that I can insert additional data into another table. Our target is SQL server 2008. We have a table which has to be populated by informatica ETLs and the application is also using the same table - so, we can't use informatica sequence generator.

In the past when I have used Oracle database, there was a Oracle sequence generator transformation available in Informatica - but for SQL server, I am not sure.

Any solutions please?

0 votes

Design a mapping to load the first record from a flat file into one table A, the last record from a flat file into table B and the remaining records into table C?

...