Hi All,
I have the following requirement some one please help me out..
I am Having a source records as followed.
ID,ID1,2400,2500,2600
-------------------------------------------
1234,1,100,,102
and I have a reference table with the following data
NAME,VALUE
---------------
2400,BANK
2500,EDU
2600,FIN
I need an out put as following
NAME,VALUE
----------
ID,1234
ID1,1
BANK,100
EDU,,
FIN,102
Actually I need to transform the column name into actual values by using reference data table.
Iam using reference data because these values will vary dynamically
Any help is appreciated.
Kind Regards
Columns into values
Moderators: chulett, rschirm, roy
Do you have any quarantees that your datatypes between columns and data are always going to be consistent (treatable as varchar)? Even if this is true, I see you having great difficulty doing this with a metadata based tool like DataStage.
You could possibly consider kicking the information you want out to an object relational data model, but reporting/extracting from that format can get sticky.
Hope that helps!
You could possibly consider kicking the information you want out to an object relational data model, but reporting/extracting from that format can get sticky.
Hope that helps!
Bestest!
John Miceli
System Specialist, MCP, MCDBA
Berkley Technology Services
"Good Morning. This is God. I will be handling all your problems today. I will not need your help. So have a great day!"
John Miceli
System Specialist, MCP, MCDBA
Berkley Technology Services
"Good Morning. This is God. I will be handling all your problems today. I will not need your help. So have a great day!"
Hi,
You can acheive this by using a server Job. Read your first line in source file and store data in a hash file like follows;
Name : HSH_FILE
Fields : NAME,REF,VALUE
KEY : NAME
Once you process the first line hash table should look like below, you can use lookup to populate REF column.
NAME REF VALUE
----- ---- --------
ID ID
ID1 ID1
2400 BANK
2500 EDU
2600 FIN
Once you done this part read your next line and populate VALUE column accodingly in the same hash file. I hope this will solve your problem.
Thanks
Upul
You can acheive this by using a server Job. Read your first line in source file and store data in a hash file like follows;
Name : HSH_FILE
Fields : NAME,REF,VALUE
KEY : NAME
Once you process the first line hash table should look like below, you can use lookup to populate REF column.
NAME REF VALUE
----- ---- --------
ID ID
ID1 ID1
2400 BANK
2500 EDU
2600 FIN
Once you done this part read your next line and populate VALUE column accodingly in the same hash file. I hope this will solve your problem.
Thanks
Upul
Thanks,
Upul
Upul
-
srinivas143
- Participant
- Posts: 30
- Joined: Tue Feb 19, 2008 11:02 pm
Hi,
I tried with your option but I am not able to do the same.
source:
ID,COUNTRY,2400
Ref data:
NAME,VALUE
2400,BANK
2500,CON
I was able to read the source columns as a record using hashfile and when I am trying to match it with lookup colunm name ..it gives me the result as below
RESULT:
ID,COUNTRY,VALUE
COUNTRY,2400,NULL
my lookup condition is 2400=NAME(I tried giving the same name as source field name also),but still I am not able to generate the required output.
Pls Help me out..
Kind Regards
I tried with your option but I am not able to do the same.
source:
ID,COUNTRY,2400
Ref data:
NAME,VALUE
2400,BANK
2500,CON
I was able to read the source columns as a record using hashfile and when I am trying to match it with lookup colunm name ..it gives me the result as below
RESULT:
ID,COUNTRY,VALUE
COUNTRY,2400,NULL
my lookup condition is 2400=NAME(I tried giving the same name as source field name also),but still I am not able to generate the required output.
Pls Help me out..
Kind Regards