replacing null column with some value and assigning a keyval

[prasad111]

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

[narasimha]

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

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 

You have a generated key 0 for rows with the replacements in your Target database. Is that what you want?

[ray.wurlod]

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.

[prasad111]

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

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.

[DSguru2B]

-Load your second table in a hashed file keyed on 'UNIQUE_KEY'.
-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)

-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.

[talk2shaanc]

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.

[prasad111]

-Load your second table in a hashed file keyed on 'UNIQUE_KEY'.
-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)

-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.

I checked out this method,
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.....................

[talk2shaanc]

To query the hash file through routine UtilityHashLookup, you need to first add your hash file to VOC

[chulett]

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.

[prasad111]

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. ...

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.

Thanks a lot for the guidance.

[chulett]

Copy the routine and change the line that comes after the 'Table is not in the cache' comment...

Code: Select all

From:   Open HashTable
To:     OpenPath HashTable

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.

[rachitha]

Hi

Prasad ,,, are you sure that you have data in your hash file..
if not .. first create that hash file and load data into it with the help of any seq file or ODBC stage. then use this hash file in "utilityhashlookup " routine.

Thanks,
Rachitha.

[prasad111]

Hi

Prasad ,,, are you sure that you have data in your hash file..
if not .. first create that hash file and load data into it with the help of any seq file or ODBC stage. then use this hash file in "utilityhashlookup " routine.

Thanks,
Rachitha.

I have the data in the hash file

[DSguru2B]

Whats the status on your problem, any progress or stuck somewhere.
Follow Craigs guideline. Its pretty straight forward.

[prasad111]

Whats the status on your problem, any progress or stuck somewhere.
Follow Craigs guideline. Its pretty straight forward.

Still unsucessful for getting the HashFile information