Each and every column in the target table (STOCK_TRADING_FCT), is derived from the existing source tables as follows.
Column Name: STCK_TRD_ID
Loading Logic:
After the source qualifier Use the ‘Sequence Generator’ Transformation. Set the Start value, interval and the end value in the properties of the sequence generator transformation. There will be two ports in the transformation Nextval and Currval. Link the nextval to the column STCK_TRD_ID (a new column and not from the source qualifier) in the ‘Expression transformation’.
Instead of the sequence generator we can use the expression itself to define the column STCK_TRD_ID1 as a first variable by checking the box V and giving the value 1 in the expression. Then adding another column STCK_TRD_ID as the second variable by checking the O check box and in the expression giving the expression as STCK_TRD_ID1+1 as below screen shot.
Column Name: SRC_CD
Loading Logic:
Have the columns NSE_TBL.SRC_ID and BSE_TBL.SRC_ID pulled from the tables NSE_TBL and BSE_TBL respectively. If the value of SRC_ID is 1 then mark the SRC_CD as NSE, if the value of SRC_ID is 2 then mark the SRC_CD as BSE. This can be mentioned in the expression transformation. The output port will be linked to the target.
Column Name: CMPNY_CD
Loading Logic:
Get the values of the columns NSE_TBL.CMPNY_CD and BSE_TBL.CMPNY_CD from the table NSE_TBL and BSE_TBL respectively. Using the code values look up in the table COMPANY_LKP for the column CMPNY_CD and get the value of COMPANY_LKP.CMPNY_DESC. This corresponding description from the COMPANY_LKP table is loaded as the STOCK_TRADING_FCT.CMPNY_CD. The link (NSE_TBL.CMPNY_CD and BSE_TBL.CMPNY_CD) from the expression transformation is mapped with the look up for COMPANY_LKP table.
Column Name: DT_ID
Loading Logic:
This column will be loaded from NSE_TBL. SYS_DT and BSE_TBL.SYS_DT.
Column Name: PRVS_DY_CLS_PRC
Loading Logic:
This column will be loaded using the values from the tables NSE_TBL.PRVS_DY_CLS_PRC and BSE_TBL.PRVS_DY_CLS_PRC. It will be the direct link from the source qualifier to the expression then to the target.
Column Name: TDY_OPN_PRC
Loading Logic:
This column will be loaded using the values from the tables NSE_TBL.TDY_OPN_PRC and BSE_TBL.TDY_OPN_PRC.It will be the direct link from the source qualifier to the expression then to the target.
Column Name: TDY_HGHST_PRC
Loading Logic:
This column will be loaded using the values from the tables NSE_TBL. TDY_HGHST_PRC and BSE_TBL. TDY_HGHST_PRC. It will be the direct link from the source qualifier to the expression then to the target.
Column Name: TDY_LWST_PRC
Loading Logic:
This column will be loaded using the values from the tables NSE_TBL.TDY_LWST_PRC and BSE_TBL. TDY_LWST_PRC. It will be the direct link from the source qualifier to the expression then to the target.
Column Name: LST_TRD_PRC
Loading Logic:
This column will be loaded using the values from the tables NSE_TBL. LST_TRD_PRC and BSE_TBL. LST_TRD_PRC. It will be the direct link from the source qualifier to the expression then to the target.
Column Name: STCK_TRD_IND
Loading Logic:
This column will be loaded using the values from below:
- 1- Look up DATE_LKP.DT_ID using STOCK_TRADING_FCT.DT_ID and get DATE_LKP.DT_VAL.
- 2- If the DATE_LKP.DT_VAL = SYS_DT then mark ‘Y’ else mark ‘N’.
Column Name: SYS_DT
Loading Logic:
This column will be loaded as system date by marking as sysdate in the expression.
Note: Informatica and its related products are Trademarks of Informatica Corporation. For more details, please visit their official website www.Informatica.com
