replacing null column with some value and assigning a keyval
Moderators: chulett, rschirm, roy
replacing null column with some value and assigning a keyval
Table 1: Source Table
FIRSTNAME--LASTNAME--FUNCTION--STARTED_YEAR
Sachin--------Tendulkar-----Bat-------1989
--------------Veerendar-----Bat----2001
Rahul------ -Dravid--- ---Bat----- --1996--
---------- -Raina----- --Bat---------2005
Irfan------- -Pathan------Bowl----- --2004
Munaf-------Patel----- --bowl----- ---2004
Table 2: Manually entered and Maintained table(This table value(column ASSIGN_VALUE) should not be hardcode anywhere)
UNIQUE_KEY----CONSTRAINT_VALUE---ASSIGN_VALUE
1-----------------ATTRIBUTE---------------UNKNOWN
2-----------------KEY_VALUE------------------0-
Result Table: Target table
GENERATED_KEY--FIRST_NAME---LAST_NAME--FUNCTION--STARTED_YEA
1---------------------Sachin---------Tendulkar ------Bat---------1989
0---------------------UNKNOWN----Veerendar-------Bat---------2001
2---------------------Rahul----------Dravid----------Bat---------1996
0----------------------[b]UNKNOWN[/b]---Raina------ ----Bat---------2005
3---------------------Irfan---------- Pathan---------Bowl---------2004
4---------------------Munaf--------- Patel------------Bowl---------2004
I am not able to get the output as shown in the target table, I was using lookup, please let me know how to di this in Datastage Server Edition 7.5.1.A
FIRSTNAME--LASTNAME--FUNCTION--STARTED_YEAR
Sachin--------Tendulkar-----Bat-------1989
--------------Veerendar-----Bat----2001
Rahul------ -Dravid--- ---Bat----- --1996--
---------- -Raina----- --Bat---------2005
Irfan------- -Pathan------Bowl----- --2004
Munaf-------Patel----- --bowl----- ---2004
Table 2: Manually entered and Maintained table(This table value(column ASSIGN_VALUE) should not be hardcode anywhere)
UNIQUE_KEY----CONSTRAINT_VALUE---ASSIGN_VALUE
1-----------------ATTRIBUTE---------------UNKNOWN
2-----------------KEY_VALUE------------------0-
Result Table: Target table
GENERATED_KEY--FIRST_NAME---LAST_NAME--FUNCTION--STARTED_YEA
1---------------------Sachin---------Tendulkar ------Bat---------1989
0---------------------UNKNOWN----Veerendar-------Bat---------2001
2---------------------Rahul----------Dravid----------Bat---------1996
0----------------------[b]UNKNOWN[/b]---Raina------ ----Bat---------2005
3---------------------Irfan---------- Pathan---------Bowl---------2004
4---------------------Munaf--------- Patel------------Bowl---------2004
I am not able to get the output as shown in the target table, I was using lookup, please let me know how to di this in Datastage Server Edition 7.5.1.A
Wellcome to DSXchange!
You can replace a null column with some value say "UNKNOWN" in you case, using
if isnull(SourceTable.FIRSTNAME) then 'UNKNOWN' else SourceTable.FIRSTNAME
I did not understand the second part when you say assign a keyval
You have a generated key 0 for rows with the replacements in your Target database. Is that what you want?
You can replace a null column with some value say "UNKNOWN" in you case, using
if isnull(SourceTable.FIRSTNAME) then 'UNKNOWN' else SourceTable.FIRSTNAME
I did not understand the second part when you say assign a keyval
Code: Select all
GEN ERATED_KEY--FIRST_NAME---LAST_NAME--FUNCTION--STARTED_YEA
1 ---------------------Sachin---------Tendulkar ------Bat---------1989
0 --------------------- UNKNOWN ----Veerendar-------Bat---------2001
2---------------------Rahul----------Dravid----------Bat---------1996
0----------------------[ b]UNKNOWN[/b]---Raina------ ----Bat---------2005
3---------------------Irfan---------- Pathan---------Bowl---------2004
4---------------------Munaf--------- Patel------------Bowl---------2004
Narasimha Kade
Finding answers is simple, all you need to do is come up with the correct questions.
Finding answers is simple, all you need to do is come up with the correct questions.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
To perform a lookup you have to have something in your source table that you can use a key into the reference table - it does not appear that your design fulfils this criterion.
I believe you are trying to accomplish two things - to generate a sequential key where FIRST_NAME and LAST_NAME both exist, and to generate a dummy value (0) where this condition is not met. The sequential key can be generated with a lookup, but is more easily generated with one of the SDK Key Management routines (which perform a lookup AND update into the SDKSequences table within DataStage).
As to the second requirement, a simple If..Then..Else expression should suffice to generate these values.
I believe you are trying to accomplish two things - to generate a sequential key where FIRST_NAME and LAST_NAME both exist, and to generate a dummy value (0) where this condition is not met. The sequential key can be generated with a lookup, but is more easily generated with one of the SDK Key Management routines (which perform a lookup AND update into the SDKSequences table within DataStage).
As to the second requirement, a simple If..Then..Else expression should suffice to generate these values.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Re: replacing null column with some value and assigning a ke
I want to get the output as shown in table 3 using table 1 and table2 and no hard coding anywhere we have to use the two tables, please let me know how can we achieve this.prasad111 wrote:Table 1: Source Table
FIRSTNAME--LASTNAME--FUNCTION--STARTED_YEAR
Sachin--------Tendulkar-----Bat-------1989
--------------Veerendar-----Bat----2001
Rahul------ -Dravid--- ---Bat----- --1996--
---------- -Raina----- --Bat---------2005
Irfan------- -Pathan------Bowl----- --2004
Munaf-------Patel----- --bowl----- ---2004
Table 2: Manually entered and Maintained table(This table value(column ASSIGN_VALUE) should not be hardcode anywhere)
UNIQUE_KEY----CONSTRAINT_VALUE---ASSIGN_VALUE
1-----------------ATTRIBUTE---------------UNKNOWN
2-----------------KEY_VALUE------------------0-
Result Table: Target table
GENERATED_KEY--FIRST_NAME---LAST_NAME--FUNCTION--STARTED_YEA
1---------------------Sachin---------Tendulkar ------Bat---------1989
0---------------------UNKNOWN----Veerendar-------Bat---------2001
2---------------------Rahul----------Dravid----------Bat---------1996
0----------------------[b]UNKNOWN[/b]---Raina------ ----Bat---------2005
3---------------------Irfan---------- Pathan---------Bowl---------2004
4---------------------Munaf--------- Patel------------Bowl---------2004
I am not able to get the output as shown in the target table, I was using lookup, please let me know how to di this in Datastage Server Edition 7.5.1.A
-Load your second table in a hashed file keyed on 'UNIQUE_KEY'.
-Specify the following stage variables
-For column 'GEN ERATED_KEY' provide derivation as 'GenKey' and for FirstName provide 'FirstNameChk'
Hope this helps. If you have to check for more columns, add more stage variables and similarly add them in the GenKey with OR operand.
-Specify the following stage variables
Code: Select all
FirstNameChk: If ISNULL(in.FIRST_NAME)=1 then
UtilityHashLookup(myHashedFileName,1,2) else
in.FIRST_NAME
GenKey: If ISNULL(in.FIRST_NAME)=1 then GenKey+1 else
UtilityHashLookup(myHashedFileName,2,2)
Hope this helps. If you have to check for more columns, add more stage variables and similarly add them in the GenKey with OR operand.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Charter Member
- Posts: 199
- Joined: Tue Jan 18, 2005 2:50 am
- Location: India
prasad,
I would suggest you should put some words to describe what is the relation between the table1 and table2. How basically you want to use them as look-up table and main table, there look-up key columns etc etc..
You need to explain what is the criteria for populating GEN ERATED_KEY fields and what role does ASSIGN_VALUE or UNIQUE_KEY or CONSTRAINT_VALUE from Table2 play in derivation of any of the column in your target.
Giving examples is good, but it should be supported by some words sometimes; otherwise sometimes (a case like this ) it becomes difficult to correlate things..and suggestion from different people will be based on some assumptions, which may or may not be help-ful to you.
I would suggest you should put some words to describe what is the relation between the table1 and table2. How basically you want to use them as look-up table and main table, there look-up key columns etc etc..
You need to explain what is the criteria for populating GEN ERATED_KEY fields and what role does ASSIGN_VALUE or UNIQUE_KEY or CONSTRAINT_VALUE from Table2 play in derivation of any of the column in your target.
Giving examples is good, but it should be supported by some words sometimes; otherwise sometimes (a case like this ) it becomes difficult to correlate things..and suggestion from different people will be based on some assumptions, which may or may not be help-ful to you.
Shantanu Choudhary
I checked out this method,DSguru2B wrote:-Load your second table in a hashed file keyed on 'UNIQUE_KEY'.
-Specify the following stage variables-For column 'GEN ERATED_KEY' provide derivation as 'GenKey' and for FirstName provide 'FirstNameChk'Code: Select all
FirstNameChk: If ISNULL(in.FIRST_NAME)=1 then UtilityHashLookup(myHashedFileName,1,2) else in.FIRST_NAME GenKey: If ISNULL(in.FIRST_NAME)=1 then GenKey+1 else UtilityHashLookup(myHashedFileName,2,2)
Hope this helps. If you have to check for more columns, add more stage variables and similarly add them in the GenKey with OR operand.
In stage variable I mentioned it like
If (IsNull(DSLink4.FIRSTNAME)=1) then
UtilityHashLookup('temp11_prasad_test',1,3) else
DSLink4.FIRSTNAME
where temp11_prasad_test is the hash file name
If (IsNull(DSLink4.FIRSTNAME)=1) then UtilityHashLookup('temp11_prasad_test',2,3) else KeyMgtGetNextValue('TEST1234')
I am getting the warning like this
PPP_TEST1..Transformer_2: The value of the row is: KEY = **TABLE NOT FOUND** FIRSTNAME = **TABLE NOT FOUND** LASTNAME = KAIF FUNCTION = BAT STARTED_YEAR = 05-JUN-2023
I checked the hash file creation UNIX folder, they were properly created and the data were also present there(do I need to mention the complete path of the hash file inside the stage varaible of the transformer), do I am missing anything.....................
-
- Charter Member
- Posts: 199
- Joined: Tue Jan 18, 2005 2:50 am
- Location: India
i have the hash files in folder "/proj/aspnd/dev/work/HFile/temp11_prasad_test", I tried to replace it in entire line Common /HashLookup/ FileHandles(100), FilesOpened but was unsuccesful, please help me where exactly I have to replace it.chulett wrote:Or you need to create a copy of the routine that can work with pathed hashed files. Hint - it's a change of one keyword in the routine. ...
Thanks a lot for the guidance.
Copy the routine and change the line that comes after the 'Table is not in the cache' comment...
That's it. And to use it, rather than send in just the name of the hashed 'table', send in the complete path. But that should be pretty obvious.
Code: Select all
From: Open HashTable
To: OpenPath HashTable
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers