Dynamically use column names as data to create new columns
Posted: Mon Apr 21, 2008 7:53 am
Please excuse me if I incorrectly approach this reply in any way as this is my first time. And I hope to explain this thoroughly.
I have the same similar situation in my work requirement of a previous posting by "offshore2002" a couple days ago. I wanted to provide a little more detail in my requirements. I have the following:
Two Inputs:
File 1, is the txn file with KEY1 & KEY2 as key and COL3, 4, 5, etc as the transaction (txn) amount columns. COL3 and greater are dynamic in nature as they could change on a quarterly basis. In other words, COL3 might be permanently dropped from the database and COL3A might be added one
quarter.
Example data:
KEY1, KEY2, COL3, COL4, COL5
------------- ------ ------ ------ ------
12-31-2007,"014", 2.99, 3.99, 1.50
12-31-2007,"101", 5.00, 22.15, 99.01
...etc...
File 2, is the lookup file containing the DB2 element mappings to the txn columns to their perspective DB2 table destination.
DB2_ELEM, TXN_COL, DB2_TBL
--------- ---------- ----------
DB2_EL1, COL4, DB2_TBL7
DB2_EL1, COL5, DB2_TBL8
DB2_EL5, COL3, DB2_TBL7
....etc....
Please note there are duplicate DB2 element names (ex. DB2_EL1) but assigned to different TXN_COL columns under different DB2 table names. So I would like to match COL* column names from File 1 to the TXN_COL data in File 2 in order to retrieve its corresponding DB2_ELEM value and DB2_TBL. All this plus carrying over the COL* column data (amount values) from File1 to its DB2 name. The end goal would look like this:
For 12-31-2007, Key2 "014":
DB2_TBL7.DB2_EL1 = 3.99
DB2_TBL7.DB2_EL5 = 2.99
DB2_TBL8.DB2_EL1 = 1.50
For 12-31-2007, Key2 "101":
DB2_TBL7.DB2_EL1 = 22.15
DB2_TBL7.DB2_EL5 = 5.00
DB2_TBL8.DB2_EL1 = 99.01
Any clues to providing a good start would be very much appreciated. I have looked over horizontal pivoting but having trouble in providing data in the screens. I apologize in my searches on the forums in not being able to find this level of detail. Again, any help is greatly appreciated. Thanks!
Ronnie
I have the same similar situation in my work requirement of a previous posting by "offshore2002" a couple days ago. I wanted to provide a little more detail in my requirements. I have the following:
Two Inputs:
File 1, is the txn file with KEY1 & KEY2 as key and COL3, 4, 5, etc as the transaction (txn) amount columns. COL3 and greater are dynamic in nature as they could change on a quarterly basis. In other words, COL3 might be permanently dropped from the database and COL3A might be added one
quarter.
Example data:
KEY1, KEY2, COL3, COL4, COL5
------------- ------ ------ ------ ------
12-31-2007,"014", 2.99, 3.99, 1.50
12-31-2007,"101", 5.00, 22.15, 99.01
...etc...
File 2, is the lookup file containing the DB2 element mappings to the txn columns to their perspective DB2 table destination.
DB2_ELEM, TXN_COL, DB2_TBL
--------- ---------- ----------
DB2_EL1, COL4, DB2_TBL7
DB2_EL1, COL5, DB2_TBL8
DB2_EL5, COL3, DB2_TBL7
....etc....
Please note there are duplicate DB2 element names (ex. DB2_EL1) but assigned to different TXN_COL columns under different DB2 table names. So I would like to match COL* column names from File 1 to the TXN_COL data in File 2 in order to retrieve its corresponding DB2_ELEM value and DB2_TBL. All this plus carrying over the COL* column data (amount values) from File1 to its DB2 name. The end goal would look like this:
For 12-31-2007, Key2 "014":
DB2_TBL7.DB2_EL1 = 3.99
DB2_TBL7.DB2_EL5 = 2.99
DB2_TBL8.DB2_EL1 = 1.50
For 12-31-2007, Key2 "101":
DB2_TBL7.DB2_EL1 = 22.15
DB2_TBL7.DB2_EL5 = 5.00
DB2_TBL8.DB2_EL1 = 99.01
Any clues to providing a good start would be very much appreciated. I have looked over horizontal pivoting but having trouble in providing data in the screens. I apologize in my searches on the forums in not being able to find this level of detail. Again, any help is greatly appreciated. Thanks!
Ronnie